/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: TbTrig.cs
**
** SAMPLE: How to use triggers on a table with the DB2 .Net Data Provider
**
** SQL Statements USED:
** CREATE TABLE
** CREATE TRIGGER
** DROP TABLE
** DROP TRIGGER
** SELECT
** INSERT
** UPDATE
** DELETE
**
** DB2 .NET Data Provider Classes USED:
** DB2Connection
** DB2Command
** DB2Transaction
**
**
*****************************************************************************
**
** Building and Running the sample program
**
** 1. Compile the TbTrig.cs file with bldapp.bat by entering the following
** at the command prompt:
**
** bldapp TbTrig
**
** or compile TbTrig.cs with the makefile by entering the following at
** the command prompt:
**
** nmake TbTrig
**
** 2. Run the TbTrig program by entering the program name at the command
** prompt:
**
** TbTrig
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing applications, see the Application
** Development Guide.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, compiling, and running DB2
** applications, visit the DB2 Information Center at
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
**
****************************************************************************/
using System;
using System.Data;
using System.IO;
using IBM.Data.DB2;
class TbTrig
{
public static void Main(String[] args)
{
// Declare a DB2Connection and a DB2Transaction
DB2Connection conn = null;
DB2Transaction trans = null;
try
{
Console.WriteLine();
Console.WriteLine(" THIS SAMPLE SHOWS HOW TO USE TRIGGERS.");
// Connect to a database
Console.WriteLine("\n Connecting to a database ...");
conn = ConnectDb(args);
// Create and demonstrate the use of a 'BEFORE INSERT' trigger
trans = conn.BeginTransaction();
TbBeforeInsertTriggerUse(conn, trans);
// Create and demonstrate the use of an 'AFTER INSERT' trigger
trans = conn.BeginTransaction();
TbAfterInsertTriggerUse(conn, trans);
// Create and demonstrate the use of a 'BEFORE DELETE' trigger
trans = conn.BeginTransaction();
TbBeforeDeleteTriggerUse(conn, trans);
// Create and demonstrate the use of a 'BEFORE UPDATE' trigger
trans = conn.BeginTransaction();
TbBeforeUpdateTriggerUse(conn, trans);
// Create and demonstrate the use of an 'AFTER UPDATE' trigger
trans = conn.BeginTransaction();
TbAfterUpdateTriggerUse(conn, trans);
// Disconnect from the database
Console.WriteLine("\n Disconnect from the database.");
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
conn.Close();
}
} // Main
public static DB2Connection ConnectDb(String[] argv)
{
String server = "";
String alias = "";
String userId = "";
String password = "";
Int32 portNumber = -1;
String connectString;
if( argv.Length > 5 ||
( argv.Length == 1 &&
( String.Compare(argv[0],"?") == 0 ||
String.Compare(argv[0],"-?") == 0 ||
String.Compare(argv[0],"/?") == 0 ||
String.Compare(argv[0],"-h",true) == 0 ||
String.Compare(argv[0],"/h",true) == 0 ||
String.Compare(argv[0],"-help",true) == 0 ||
String.Compare(argv[0],"/help",true) == 0 ) ) )
{
throw new Exception(
"Usage: prog_name [dbAlias] [userId passwd] \n" +
" prog_name [dbAlias] server portNum userId passwd");
}
switch (argv.Length)
{
case 0: // Use all defaults
alias = "sample";
userId = "";
password = "";
break;
case 1: // dbAlias specified
alias = argv[0];
userId = "";
password = "";
break;
case 2: // userId & passwd specified
alias = "sample";
userId = argv[0];
password = argv[1];
break;
case 3: // dbAlias, userId & passwd specified
alias = argv[0];
userId = argv[1];
password = argv[2];
break;
case 4: // use default dbAlias
alias = "sample";
server = argv[0];
portNumber = Convert.ToInt32(argv[1]);
userId = argv[2];
password = argv[3];
break;
case 5: // everything specified
alias = argv[0];
server = argv[1];
portNumber = Convert.ToInt32(argv[2]);
userId = argv[3];
password = argv[4];
break;
}
if(portNumber==-1)
{
connectString = "Database=" + alias;
}
else
{
connectString = "Server=" + server + ":" + portNumber +
";Database=" + alias;
}
if(userId != "")
{
connectString += ";UID=" + userId + ";PWD=" + password;
}
DB2Connection conn = new DB2Connection(connectString);
conn.Open();
Console.WriteLine(" Connected to the " + alias + " database");
return conn;
} // ConnectDb
// Helping method: Display content from the 'staff' table
public static void StaffTbContentDisplay(DB2Connection conn,
DB2Transaction trans)
{
try
{
Int16 id = 0;
String name = null;
Int16 dept = 0;
String job = null;
Int16 years = 0;
Decimal salary = 0;
Decimal comm = 0;
Console.WriteLine();
Console.WriteLine(
" SELECT * FROM staff WHERE id <= 50\n\n" +
" ID NAME DEPT JOB YEARS SALARY COMM\n" +
" --- -------- ---- ----- ----- -------- --------");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM staff WHERE id <= 50";
cmd.Transaction = trans;
DB2DataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
id = reader.GetInt16(0);
name = reader.GetString(1);
dept = reader.GetInt16(2);
job = reader.GetString(3);
if (reader.IsDBNull(4))
{
years = 0;
}
else
{
years = reader.GetInt16(4);
}
salary = reader.GetDecimal(5);
if ( reader.IsDBNull(6) )
{
comm = 0;
}
else
{
comm = reader.GetDecimal(6);
}
Console.Write(" " + Format(id, 3) +
" " + Format(name, 8) +
" " + Format(dept, 4));
if (job != null)
{
Console.Write(" " + Format(job, 5));
}
else
{
Console.Write(" -");
}
if (years != 0)
{
Console.Write(" " + Format(years, 5));
}
else
{
Console.Write(" -");
}
Console.Write(" " + Format(salary, 7, 2));
if (comm != 0)
{
Console.Write(" " + Format(comm, 7, 2));
}
else
{
Console.Write(" -");
}
Console.WriteLine();
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // StaffTbContentDisplay
// Helping method: This method takes a String and returns it with
// length 'finalLen'
public static String Format (String strData, int finalLen)
{
String finalStr;
if (finalLen <= strData.Length)
{
finalStr = strData.Substring(0, finalLen);
}
else
{
finalStr = strData;
for (int i = strData.Length; i < finalLen; i++)
{
finalStr = finalStr + " ";
}
}
return (finalStr);
} // Format(String, int)
// Helping method: This method takes an Int16 and returns it as a String
// with length 'finalLen'
public static String Format(Int16 intData, int finalLen)
{
String strData = intData.ToString();
String finalStr = null;
if (finalLen <= strData.Length)
{
finalStr = strData.Substring(0, finalLen);
}
else
{
finalStr = "";
for (int i = 0; i < finalLen - strData.Length; i++)
{
finalStr = finalStr + " ";
}
finalStr = finalStr + strData;
}
return (finalStr);
} // Format(Int16, int)
// Helping method: This method takes a Decimal and returns it as a String
// with a specified precision and scale
public static String Format(Decimal doubData, int precision, int scale)
{
Decimal dataRound = Decimal.Round(doubData,scale);
String strData = String.Format("{0:f"+scale+"}" ,dataRound);
// Prepare the final string
int finalLen = precision + 1;
String finalStr;
if (finalLen <= strData.Length)
{
finalStr = strData.Substring(0, finalLen);
}
else
{
finalStr = "";
for (int i = 0; i < finalLen - strData.Length; i++)
{
finalStr = finalStr + " ";
}
finalStr = finalStr + strData;
}
return (finalStr);
} // Format(Decimal, int, int)
// Helping method: This method creates a table 'staff_stats'
// and inserts some values into it
public static void StaffStatsTbCreate(DB2Connection conn,
DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(" CREATE TABLE staff_stats(nbemp SMALLINT)");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TABLE staff_stats(nbemp SMALLINT)";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(
" INSERT INTO staff_stats VALUES(SELECT COUNT(*) FROM staff)");
cmd.CommandText = "INSERT INTO staff_stats " +
" VALUES(SELECT COUNT(*) " +
" FROM staff)";
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // StaffStatsTbCreate
// Helping method: This method displays the contents of the
// 'staff_stats' table
public static void StaffStatsTbContentDisplay(DB2Connection conn,
DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(" SELECT nbemp FROM staff_stats");
Console.WriteLine(" NBEMP\n" + " -----");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM staff_stats";
cmd.Transaction = trans;
DB2DataReader reader = cmd.ExecuteReader();
reader.Read();
Console.WriteLine(" " + Format(reader.GetInt16(0),5));
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // StaffStatsTbContentDisplay
// Helping method: This method drops the 'staff_stats' table
public static void StaffStatsTbDrop(DB2Connection conn,
DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(" DROP TABLE staff_stats");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "DROP TABLE staff_stats";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // StaffStatsTbDrop
// Helping method: This method creates the 'salary_status' table
// and inserts some values into it
public static void SalaryStatusTbCreate(DB2Connection conn,
DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(
" CREATE TABLE salary_status(emp_name VARCHAR(9),\n" +
" sal DECIMAL(7, 2),\n" +
" status CHAR(15))");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText =
"CREATE TABLE salary_status(emp_name VARCHAR(9), " +
" sal DECIMAL(7, 2), " +
" status CHAR(15))";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(
" INSERT INTO salary_status\n" +
" SELECT name, salary, 'Not Defined'\n" +
" FROM staff\n" +
" WHERE id <= 50");
cmd.CommandText = "INSERT INTO salary_status " +
" SELECT name, salary, 'Not Defined' " +
" FROM staff " +
" WHERE id <= 50";
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // SalaryStatusTbCreate
// Helping method: This method displays the contents of the
// 'salary_status' table
public static void SalaryStatusTbContentDisplay(DB2Connection conn,
DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(" SELECT * FROM salary_status");
Console.WriteLine(" EMP_NAME SALARY STATUS\n" +
" ---------- -------- ----------------");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM salary_status";
cmd.Transaction = trans;
DB2DataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(
" " +
Format(reader.GetString(0),10) + " " +
Format(reader.GetDecimal(1),7,2) + " " +
Format(reader.GetString(2),15));
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // SalaryStatusTbContentDisplay
// Helping method: This method drops the 'salary_status' table
public static void SalaryStatusTbDrop(DB2Connection conn,
DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(" DROP TABLE salary_status");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "DROP TABLE salary_status";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // SalaryStatusTbDrop
// Helping method: This method creates a table 'salary_history' and
// inserts some values into it
public static void SalaryHistoryTbCreate(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
" CREATE TABLE salary_history(employee_name VARCHAR(9),\n" +
" salary_record DECIMAL(7, 2),\n" +
" change_date DATE)");
try
{
DB2Command cmd = conn.CreateCommand();
cmd.CommandText =
"CREATE TABLE salary_history(employee_name VARCHAR(9), " +
" salary_record DECIMAL(7, 2), " +
" change_date DATE)";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // SalaryHistoryTbCreate
// Helping method: This method displays the contents of the
// 'salary_history' table
public static void SalaryHistoryTbContentDisplay(DB2Connection conn,
DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(" SELECT * FROM salary_history");
Console.WriteLine(" EMPLOYEE_NAME SALARY_RECORD CHANGE_DATE\n" +
" -------------- -------------- -----------");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM salary_history";
cmd.Transaction = trans;
DB2DataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(" " +
Format(reader.GetString(0),14) + " " +
Format(reader.GetDecimal(1),13,2) + " " +
reader.GetDate(2));
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // SalaryHistoryTbContentDisplay
// Helping method: This method drops the 'salary_history' table
public static void SalaryHistoryTbDrop(DB2Connection conn,
DB2Transaction trans)
{
try
{
Console.WriteLine();
Console.WriteLine(" DROP TABLE salary_history");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "DROP TABLE salary_history";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // SalaryHistoryTbDrop
// This method creates and demonstrates the use of a 'BEFORE INSERT'
// trigger
public static void TbBeforeInsertTriggerUse(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENTS:\n" +
" CREATE TRIGGER\n" +
" COMMIT\n" +
" INSERT\n" +
" ROLLBACK\n" +
" DROP TRIGGER\n" +
" TO SHOW A 'BEFORE INSERT' TRIGGER.");
// Display the initial content of the 'staff' table
StaffTbContentDisplay(conn, trans);
// Create a 'BEFORE INSERT' trigger
try
{
Console.WriteLine();
Console.WriteLine(" CREATE TRIGGER min_salary\n" +
" NO CASCADE BEFORE INSERT\n" +
" ON staff\n" +
" REFERENCING NEW AS newstaff\n" +
" FOR EACH ROW MODE DB2SQL\n" +
" BEGIN ATOMIC\n" +
" SET newstaff.salary =\n" +
" CASE\n" +
" WHEN newstaff.job = 'Mgr' AND\n" +
" newstaff.salary < 17000.00\n" +
" THEN 17000.00\n" +
" WHEN newstaff.job = 'Sales' AND\n" +
" newstaff.salary < 14000.00\n" +
" THEN 14000.00\n" +
" WHEN newstaff.job = 'Clerk' AND\n" +
" newstaff.salary < 10000.00\n" +
" THEN 10000.00\n" +
" ELSE newstaff.salary\n" +
" END;\n" +
" END");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "CREATE TRIGGER min_salary " +
" NO CASCADE BEFORE INSERT " +
" ON staff " +
" REFERENCING NEW AS newstaff " +
" FOR EACH ROW MODE DB2SQL " +
" BEGIN ATOMIC " +
" SET newstaff.salary = " +
" CASE " +
" WHEN newstaff.job = 'Mgr' AND " +
" newstaff.salary < 17000.00 " +
" THEN 17000.00 " +
" WHEN newstaff.job = 'Sales' AND " +
" newstaff.salary < 14000.00 " +
" THEN 14000.00 " +
" WHEN newstaff.job = 'Clerk' AND " +
" newstaff.salary < 10000.00 " +
" THEN 10000.00 " +
" ELSE newstaff.salary " +
" END; " +
" END";
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Insert table data using values
try
{
Console.WriteLine();
Console.WriteLine(
" Invoke the statement:\n" +
" INSERT INTO staff(id, name, dept, job, salary)\n" +
" VALUES(25, 'Pearce', 38, 'Clerk', 7217.50),\n" +
" (35, 'Hachey', 38, 'Mgr', 21270.00),\n" +
" (45, 'Wagland', 38, 'Sales', 11575.00)");
trans = conn.BeginTransaction();
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText =
"INSERT INTO staff(id, name, dept, job, salary) " +
" VALUES(25, 'Pearce', 38, 'Clerk', 7217.50), " +
" (35, 'Hachey', 38, 'Mgr', 21270.00), " +
" (45, 'Wagland', 38, 'Sales', 11575.00)";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Display the final content of the 'staff' table
StaffTbContentDisplay(conn, trans);
// Drop the trigger
try
{
Console.WriteLine();
Console.WriteLine(" Rollback the transaction.");
trans.Rollback();
Console.WriteLine();
Console.WriteLine(" DROP TRIGGER min_salary");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "DROP TRIGGER min_salary";
trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // TbBeforeInsertTriggerUse
// This method creates and demonstrates the use of an 'AFTER INSERT'
// trigger
public static void TbAfterInsertTriggerUse(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENTS:\n" +
" CREATE TRIGGER\n" +
" COMMIT\n" +
" INSERT\n" +
" ROLLBACK\n" +
" DROP TRIGGER\n" +
" TO SHOW AN 'AFTER INSERT' TRIGGER.");
// Create a table called 'staff_stats'
StaffStatsTbCreate(conn, trans);
// Display the content of the 'staff_stats' table
trans = conn.BeginTransaction();
StaffStatsTbContentDisplay(conn, trans);
// Create an 'AFTER INSERT' trigger
try
{
Console.WriteLine();
Console.WriteLine(" CREATE TRIGGER new_hire\n" +
" AFTER INSERT\n" +
" ON staff\n" +
" FOR EACH ROW MODE DB2SQL\n" +
" BEGIN ATOMIC\n" +
" UPDATE staff_stats\n" +
" SET nbemp = nbemp + 1;\n" +
" END");
DB2Command cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandText = "CREATE TRIGGER new_hire " +
" AFTER INSERT " +
" ON staff " +
" FOR EACH ROW MODE DB2SQL " +
" BEGIN ATOMIC " +
" UPDATE staff_stats " +
" SET nbemp = nbemp + 1; " +
" END";
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Insert table data using values
try
{
Console.WriteLine();
Console.WriteLine(
" Invoke the statement:\n" +
" INSERT INTO staff(id, name, dept, job, salary)\n" +
" VALUES(25, 'Pearce', 38, 'Clerk', 7217.50),\n" +
" (35, 'Hachey', 38, 'Mgr', 21270.00),\n" +
" (45, 'Wagland', 38, 'Sales', 11575.00)");
DB2Command cmd = conn.CreateCommand();
trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.CommandText =
"INSERT INTO staff(id, name, dept, job, salary) " +
" VALUES(25, 'Pearce' , 38, 'Clerk', 7217.50), " +
" (35, 'Hachey' , 38, 'Mgr' , 21270.00), " +
" (45, 'Wagland', 38, 'Sales', 11575.00)";
cmd.ExecuteNonQuery();
// Display the content of the 'staff_stats' table
StaffStatsTbContentDisplay(conn, trans);
// Rollback the transaction
Console.WriteLine();
Console.WriteLine(" Rollback the transaction.");
trans.Rollback();
// Drop the trigger
Console.WriteLine();
Console.WriteLine(" DROP TRIGGER new_hire");
trans = conn.BeginTransaction();
cmd.CommandText = "DROP TRIGGER new_hire";
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the 'staff_stats' table
trans = conn.BeginTransaction();
StaffStatsTbDrop(conn, trans);
} // TbAfterInsertTriggerUse
// This method creates and demonstrates the use of a 'BEFORE DELETE'
// trigger
public static void TbBeforeDeleteTriggerUse(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENTS:\n" +
" CREATE TRIGGER\n" +
" COMMIT\n" +
" DELETE\n" +
" ROLLBACK\n" +
" DROP TRIGGER\n" +
" TO SHOW A 'BEFORE DELETE' TRIGGER.");
// Display the initial content of the 'staff' table
StaffTbContentDisplay(conn, trans);
// Create a 'BEFORE DELETE' trigger
try
{
Console.WriteLine();
Console.WriteLine(" CREATE TRIGGER do_not_del_sales\n" +
" NO CASCADE BEFORE DELETE\n" +
" ON staff\n" +
" REFERENCING OLD AS oldstaff\n" +
" FOR EACH ROW MODE DB2SQL\n" +
" WHEN (oldstaff.job = 'Sales')\n" +
" BEGIN ATOMIC\n" +
" SIGNAL SQLSTATE '75000' " +
" ('Sales can not be deleted now.');\n" +
" END");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TRIGGER do_not_del_sales " +
" NO CASCADE BEFORE DELETE " +
" ON staff " +
" REFERENCING OLD AS oldstaff " +
" FOR EACH ROW MODE DB2SQL " +
" WHEN (oldstaff.job = 'Sales') " +
" BEGIN ATOMIC " +
" SIGNAL SQLSTATE '75000' " +
" ('Sales can not be deleted now.'); " +
" END";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Delete data from the 'staff' table
try
{
Console.WriteLine();
Console.WriteLine(" Invoke the statement:\n" +
" DELETE FROM staff WHERE id <= 50");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM staff WHERE id <= 50";
trans = conn.BeginTransaction();
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine();
Console.WriteLine(
"**************** Expected Error ******************\n");
Console.WriteLine(e.Message);
Console.WriteLine(
"**************************************************");
}
// Display the final content of the 'staff' table
StaffTbContentDisplay(conn, trans);
// Drop the trigger
try
{
Console.WriteLine();
Console.WriteLine(" Rollback the transaction.");
trans.Rollback();
Console.WriteLine();
Console.WriteLine(" DROP TRIGGER do_not_del_sales");
DB2Command cmd = conn.CreateCommand();
trans = conn.BeginTransaction();
cmd.CommandText = "DROP TRIGGER do_not_del_sales";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
} // TbBeforeDeleteTriggerUse
// This method creates and demonstrates the use of a 'BEFORE DELETE'
// trigger
public static void TbBeforeUpdateTriggerUse(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENTS:\n" +
" CREATE TRIGGER\n" +
" COMMIT\n" +
" UPDATE\n" +
" ROLLBACK\n" +
" DROP TRIGGER\n" +
" TO SHOW A 'BEFORE UPDATE' TRIGGER.");
// Create a table called salary_status
SalaryStatusTbCreate(conn, trans);
// Display the content of the 'salary_status' table
SalaryStatusTbContentDisplay(conn, trans);
// Create a 'BEFORE UPDATE' trigger
try
{
Console.WriteLine();
Console.WriteLine(" CREATE TRIGGER salary_status\n" +
" NO CASCADE BEFORE UPDATE OF sal\n" +
" ON salary_status\n" +
" REFERENCING NEW AS new OLD AS old\n" +
" FOR EACH ROW MODE DB2SQL\n" +
" BEGIN ATOMIC\n" +
" SET new.status =\n" +
" CASE\n" +
" WHEN new.sal < old.sal\n" +
" THEN 'Decreasing'\n" +
" WHEN new.sal > old.sal\n" +
" THEN 'Increasing'\n" +
" END;\n" +
" END");
DB2Command cmd = conn.CreateCommand();
trans = conn.BeginTransaction();
cmd.CommandText = "CREATE TRIGGER sal_status " +
" NO CASCADE BEFORE UPDATE OF sal " +
" ON salary_status " +
" REFERENCING NEW AS new OLD AS old " +
" FOR EACH ROW MODE DB2SQL " +
" BEGIN ATOMIC " +
" SET new.status = " +
" CASE " +
" WHEN new.sal < old.sal " +
" THEN 'Decreasing' " +
" WHEN new.sal > old.sal " +
" THEN 'Increasing' " +
" END; " +
" END ";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Update data in table 'salary_status'
try
{
Console.WriteLine();
Console.WriteLine(
" Invoke the statement:\n" +
" UPDATE salary_status SET sal = 18000.00");
DB2Command cmd = conn.CreateCommand();
trans = conn.BeginTransaction();
cmd.CommandText = "UPDATE salary_status SET sal = 18000.00";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Display the content of the 'salary_status' table
SalaryStatusTbContentDisplay(conn, trans);
// Rollback the transaction
try
{
Console.WriteLine();
Console.WriteLine(" Rollback the transaction.");
trans.Rollback();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the trigger
try
{
Console.WriteLine();
Console.WriteLine(" DROP TRIGGER sal_status");
DB2Command cmd = conn.CreateCommand();
trans = conn.BeginTransaction();
cmd.CommandText = "DROP TRIGGER sal_status";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop salary_status table
trans = conn.BeginTransaction();
SalaryStatusTbDrop(conn, trans);
} // TbBeforeUpdateTriggerUse
// This method creates and demonstrates the use of an 'AFTER UPDATE'
// trigger
public static void TbAfterUpdateTriggerUse(DB2Connection conn,
DB2Transaction trans)
{
Console.WriteLine();
Console.WriteLine(
" ----------------------------------------------------------\n" +
" USE THE SQL STATEMENTS:\n" +
" CREATE TRIGGER\n" +
" COMMIT\n" +
" UPDATE\n" +
" DROP TRIGGER\n" +
" TO SHOW AN 'AFTER UPDATE' TRIGGER.");
// Create a table called 'salary_history'
SalaryHistoryTbCreate(conn, trans);
// Display the content of the 'salary_history' table
trans = conn.BeginTransaction();
SalaryHistoryTbContentDisplay(conn, trans);
try
{
Console.WriteLine();
Console.WriteLine(" CREATE TRIGGER sal_history\n" +
" AFTER UPDATE OF salary\n" +
" ON staff\n" +
" REFERENCING NEW AS newstaff\n" +
" FOR EACH ROW MODE DB2SQL\n" +
" BEGIN ATOMIC\n" +
" INSERT INTO salary_history\n" +
" VALUES(newstaff.name,\n" +
" newstaff.salary,\n" +
" CURRENT DATE);\n" +
" END");
DB2Command cmd = conn.CreateCommand();
cmd.CommandText = "CREATE TRIGGER sal_history " +
" AFTER UPDATE OF salary " +
" ON staff " +
" REFERENCING NEW AS newstaff " +
" FOR EACH ROW MODE DB2SQL " +
" BEGIN ATOMIC " +
" INSERT INTO salary_history " +
" VALUES(newstaff.name, " +
" newstaff.salary, " +
" CURRENT DATE); " +
" END";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Update table data
try
{
Console.WriteLine();
Console.WriteLine(
" Invoke the statement:\n" +
" UPDATE staff SET salary = 20000.00 WHERE name = 'Sanders'");
DB2Command cmd = conn.CreateCommand();
trans = conn.BeginTransaction();
cmd.CommandText =
"UPDATE staff SET salary = 20000.00 WHERE name = 'Sanders'";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(
" Invoke the statement:\n" +
" UPDATE staff SET salary = 21000.00 WHERE name = 'Sanders'");
cmd.CommandText =
"UPDATE staff SET salary = 21000.00 WHERE name = 'Sanders'";
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(
" Invoke the statement:\n" +
" UPDATE staff SET salary = 23000.00 WHERE name = 'Sanders'");
cmd.CommandText =
"UPDATE staff SET salary = 23000.00 WHERE name = 'Sanders'";
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(
" Invoke the statement:\n" +
" UPDATE staff SET salary = 20000.00 WHERE name = 'Hanes'");
cmd.CommandText =
"UPDATE staff SET salary = 20000.00 WHERE name = 'Hanes'";
cmd.ExecuteNonQuery();
Console.WriteLine();
Console.WriteLine(
" Invoke the statement:\n" +
" UPDATE staff SET salary = 21000.00 WHERE name = 'Hanes'");
cmd.CommandText =
"UPDATE staff SET salary = 21000.00 WHERE name = 'Hanes'";
cmd.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Display the content of the 'salary_history' table
SalaryHistoryTbContentDisplay(conn, trans);
// Rollback the transaction
try
{
Console.WriteLine();
Console.WriteLine(" Rollback the transaction.");
trans.Rollback();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the trigger
try
{
Console.WriteLine();
Console.WriteLine(" DROP TRIGGER sal_history");
DB2Command cmd = conn.CreateCommand();
trans = conn.BeginTransaction();
cmd.CommandText = "DROP TRIGGER sal_history";
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Drop the 'salary_history' table
trans = conn.BeginTransaction();
SalaryHistoryTbDrop(conn, trans);
} // TbAfterUpdateTriggerUse
} // TbTrig