'****************************************************************************
' (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.vb
'
' 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.vb file with bldapp.bat by entering the following
' at the command prompt:
'
' bldapp TbTrig
'
' or compile TbTrig.vb 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
'
'****************************************************************************
Imports System
Imports System.Data
Imports Microsoft.VisualBasic
Imports IBM.Data.DB2
Public Class TbTrig
Public Shared Sub Main(args() As String)
' Declare a DB2Connection and a DB2Transaction
Dim conn As DB2Connection
Dim trans As DB2Transaction
Try
Console.WriteLine()
Console.WriteLine(" THIS SAMPLE SHOWS HOW TO USE TRIGGERS.")
' Connect to a database
Console.WriteLine(vbNewLine & " 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(vbNewLine & _
" Disconnect from the database.")
conn.Close()
Catch e As Exception
Console.WriteLine(e.Message)
If Not (conn is System.DBNull.value) Then
conn.Close()
End If
End Try
End Sub ' Main
' This method establishes a connection to a database
Public Shared Function ConnectDb(argv() As String) As DB2Connection
Dim server As String
Dim dbalias As String
Dim userId As String
Dim password As String
Dim portNumber As Int32 = -1
Dim connectString As String
If (argv.Length > 5) Then
Throw new Exception( _
"Usage: prog_name [dbAlias] [userId passwd]" & vbNewLine & _
" prog_name [dbAlias] server portNum userId passwd")
Else
If (argv.Length = 1) Then
If( String.Compare(argv(0),"?") = 0 Or _
String.Compare(argv(0),"-?") = 0 Or _
String.Compare(argv(0),"/?") = 0 Or _
String.Compare(argv(0),"-h",true) = 0 Or _
String.Compare(argv(0),"/h",true) = 0 Or _
String.Compare(argv(0),"-help",true) = 0 Or _
String.Compare(argv(0),"/help",true) = 0 ) Then
Throw new Exception( _
"Usage: prog_name [dbAlias] [userId passwd]" & vbNewLine & _
" prog_name [dbAlias] server portNum userId passwd")
End If
End If
End If
Select Case (argv.Length)
Case 0 ' Use all defaults
dbalias = "sample"
userId = ""
password = ""
Case 1 ' dbAlias specified
dbalias = argv(0)
userId = ""
password = ""
Case 2 ' userId & passwd specified
dbalias = "sample"
userId = argv(0)
password = argv(1)
Case 3 ' dbAlias, userId & passwd specified
dbalias = argv(0)
userId = argv(1)
password = argv(2)
Case 4 ' use default dbAlias
dbalias = "sample"
server = argv(0)
portNumber = Convert.ToInt32(argv(1))
userId = argv(2)
password = argv(3)
Case 5 ' everything specified
dbalias = argv(0)
server = argv(1)
portNumber = Convert.ToInt32(argv(2))
userId = argv(3)
password = argv(4)
End Select
If(portNumber = -1) Then
connectString = "Database=" & dbalias
Else
connectString = "Server=" & server & ":" & portNumber & _
";Database=" & dbalias
End If
If (userId <> "")
connectString += ";UID=" & userId & ";PWD=" & password
End If
Dim conn As DB2Connection = new DB2Connection(connectString)
conn.Open()
Console.WriteLine(" Connected to the " & dbalias & " database")
Return conn
End Function ' ConnectDb
' Helping method: Display content from the 'staff' table
Public Shared Sub StaffTbContentDisplay(conn As DB2Connection, _
trans As DB2Transaction)
Try
Dim id As Int16 = 0
Dim name As String
Dim dept As Int16 = 0
Dim job As String
Dim years As Int16 = 0
Dim salary As Decimal = 0
Dim comm As Decimal = 0
Console.WriteLine()
Console.WriteLine( _
" SELECT * FROM staff WHERE id <= 50" & vbNewLine & vbNewLine & _
" ID NAME DEPT JOB YEARS SALARY COMM" & vbNewLine & _
" --- -------- ---- ----- ----- -------- --------")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM staff WHERE id <= 50"
cmd.Transaction = trans
Dim reader As DB2DataReader = cmd.ExecuteReader()
Do While reader.Read()
id = reader.GetInt16(0)
name = reader.GetString(1)
dept = reader.GetInt16(2)
job = reader.GetString(3)
If (reader.IsDBNull(4)) Then
years = 0
Else
years = reader.GetInt16(4)
End If
salary = reader.GetDecimal(5)
If ( reader.IsDBNull(6) ) Then
comm = 0
Else
comm = reader.GetDecimal(6)
End If
Console.Write(" " & Format(id, 3) & " " & Format(name, 8) & _
" " & Format(dept, 4))
If (job is System.DBNull.value) Then
Console.Write(" -")
Else
Console.Write(" " & Format(job, 5))
End If
If (years <> 0) Then
Console.Write(" " & Format(years, 5))
Else
Console.Write(" -")
End If
Console.Write(" " & Format(salary, 7, 2))
If (comm <> 0) Then
Console.Write(" " & Format(comm, 7, 2))
Else
Console.Write(" -")
End If
Console.WriteLine()
Loop
reader.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' StaffTbContentDisplay
' Helping method: This method takes a String and returns it with
' length 'finalLen'
Public Shared Function Format (strData As String, _
finalLen As Integer) As String
Dim finalStr As String
If (finalLen <= strData.Length) Then
finalStr = strData.Substring(0, finalLen)
Else
finalStr = strData
Dim i As Integer
For i = strData.Length to (finalLen-1)
finalStr = finalStr & " "
Next i
End If
Return finalStr
End Function ' Format(String, Integer)
' Helping method: This method takes an Int16 and returns it as a String
' with length 'finalLen'
Public Shared Function Format (IntData As Int16, _
finalLen As Integer) As String
Dim strData As String = IntData.ToString()
Dim finalStr As String
If (finalLen <= strData.Length) Then
finalStr = strData.Substring(0, finalLen)
Else
finalStr = ""
Dim i As Integer
For i = 0 to (finalLen - strData.Length - 1)
finalStr = finalStr & " "
Next i
finalStr = finalStr & strData
End If
Return finalStr
End Function ' Format(Int16, Integer)
' Helping method: This method takes a Decimal and returns it as a String
' with a specified precision and scale
Public Shared Function Format (doubData As Decimal, _
precision As Integer, _
scale As Integer) As String
Dim dataRound As Decimal = Decimal.Round(doubData,scale)
Dim strData As String = String.Format("{0:f" & scale & "}",dataRound)
' Prepare the final string
Dim finalLen As Integer = precision + 1
Dim finalStr As String
If (finalLen <= strData.Length) Then
finalStr = strData.Substring(0, finalLen)
Else
finalStr = ""
Dim i As Integer
For i = 0 to (finalLen - strData.Length-1)
finalStr = finalStr & " "
Next i
finalStr = finalStr & strData
End If
Return finalStr
End Function ' Format(Decimal, Integer, Integer)
' Helping method: This method creates a table 'staff_stats'
' and inserts some values into it
Public Shared Sub StaffStatsTbCreate(conn As DB2Connection, _
trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine(" CREATE TABLE staff_stats(nbemp SMALLInt)")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' StaffStatsTbCreate
' Helping method: This method displays the contents of the
' 'staff_stats' table
Public Shared Sub StaffStatsTbContentDisplay(conn As DB2Connection, _
trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine(" SELECT nbemp FROM staff_stats")
Console.WriteLine(" NBEMP" & vbNewLine & _
" -----")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM staff_stats"
cmd.Transaction = trans
Dim reader As DB2DataReader = cmd.ExecuteReader()
reader.Read()
Console.WriteLine(" " & Format(reader.GetInt16(0),5))
reader.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' StaffStatsTbContentDisplay
' Helping method: This method drops the 'staff_stats' table
Public Shared Sub StaffStatsTbDrop(conn As DB2Connection, _
trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine(" DROP TABLE staff_stats")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "DROP TABLE staff_stats"
cmd.Transaction = trans
cmd.ExecuteNonQuery()
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' StaffStatsTbDrop
' Helping method: This method creates the 'salary_status' table
' and inserts some values into it
Public Shared Sub SalaryStatusTbCreate(conn As DB2Connection, _
trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine( _
" CREATE TABLE salary_status(emp_name VARCHAR(9)," & vbNewLine & _
" sal DECIMAL(7, 2)," & vbNewLine & _
" status CHAR(15))")
Dim cmd As DB2Command = 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" & vbNewLine & _
" SELECT name, salary, 'Not Defined'" & vbNewLine & _
" FROM staff" & vbNewLine & _
" WHERE id <= 50")
cmd.CommandText = "INSERT IntO salary_status " & _
" SELECT name, salary, 'Not Defined' " & _
" FROM staff " & _
" WHERE id <= 50"
cmd.ExecuteNonQuery()
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' SalaryStatusTbCreate
' Helping method: This method displays the contents of the
' 'salary_status' table
Public Shared Sub SalaryStatusTbContentDisplay(conn As DB2Connection, _
trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine(" SELECT * FROM salary_status")
Console.WriteLine(" EMP_NAME SALARY STATUS" & vbNewLine & _
" ---------- -------- ----------------")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM salary_status"
cmd.Transaction = trans
Dim reader As DB2DataReader = cmd.ExecuteReader()
Do While reader.Read()
Console.WriteLine(" " & _
Format(reader.GetString(0),10) & " " & _
Format(reader.GetDecimal(1),7,2) & " " & _
Format(reader.GetString(2),15))
Loop
reader.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' SalaryStatusTbContentDisplay
' Helping method: This method drops the 'salary_status' table
Public Shared Sub SalaryStatusTbDrop(conn As DB2Connection, _
trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine(" DROP TABLE salary_status")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "DROP TABLE salary_status"
cmd.Transaction = trans
cmd.ExecuteNonQuery()
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' SalaryStatusTbDrop
' Helping method: This method creates a table 'salary_history' and
' inserts some values into it
Public Shared Sub SalaryHistoryTbCreate(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
" CREATE TABLE salary_history(employee_name VARCHAR(9)," & _
vbNewLine & _
" salary_record DECIMAL(7, 2)," & _
vbNewLine & _
" change_date DATE)")
Try
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' SalaryHistoryTbCreate
' Helping method: This method displays the contents of the
' 'salary_history' table
Public Shared Sub SalaryHistoryTbContentDisplay(conn As DB2Connection, _
trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine(" SELECT * FROM salary_history")
Console.WriteLine( _
" EMPLOYEE_NAME SALARY_RECORD CHANGE_DATE" & vbNewLine & _
" -------------- -------------- -----------")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "SELECT * FROM salary_history"
cmd.Transaction = trans
Dim reader As DB2DataReader = cmd.ExecuteReader()
Do While reader.Read()
Console.WriteLine(" " & _
Format(reader.GetString(0),14) & " " & _
Format(reader.GetDecimal(1),13,2) & " " & _
reader.GetDate(2))
Loop
reader.Close()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' SalaryHistoryTbContentDisplay
' Helping method: This method drops the 'salary_history' table
Public Shared Sub SalaryHistoryTbDrop(conn As DB2Connection, _
trans As DB2Transaction)
Try
Console.WriteLine()
Console.WriteLine(" DROP TABLE salary_history")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "DROP TABLE salary_history"
cmd.Transaction = trans
cmd.ExecuteNonQuery()
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' SalaryHistoryTbDrop
' This method creates and demonstrates the use of a 'BEFORE INSERT'
' trigger
Public Shared Sub TbBeforeInsertTriggerUse(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" CREATE TRIGGER" & vbNewLine & _
" COMMIT" & vbNewLine & _
" INSERT" & vbNewLine & _
" ROLLBACK" & vbNewLine & _
" DROP TRIGGER" & vbNewLine & _
" 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" & vbNewLine & _
" NO CASCADE BEFORE INSERT" & vbNewLine & _
" ON staff" & vbNewLine & _
" REFERENCING NEW AS newstaff" & vbNewLine & _
" FOR EACH ROW MODE DB2SQL" & vbNewLine & _
" BEGIN ATOMIC" & vbNewLine & _
" SET newstaff.salary =" & vbNewLine & _
" CASE" & vbNewLine & _
" WHEN newstaff.job = 'Mgr' AND" & vbNewLine & _
" newstaff.salary < 17000.00" & vbNewLine & _
" THEN 17000.00" & vbNewLine & _
" WHEN newstaff.job = 'Sales' AND" & vbNewLine & _
" newstaff.salary < 14000.00" & vbNewLine & _
" THEN 14000.00" & vbNewLine & _
" WHEN newstaff.job = 'Clerk' AND" & vbNewLine & _
" newstaff.salary < 10000.00" & vbNewLine & _
" THEN 10000.00" & vbNewLine & _
" Else newstaff.salary" & vbNewLine & _
" END;" & vbNewLine & _
" END")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Insert table data using values
Try
Console.WriteLine()
Console.WriteLine( _
" Invoke the statement:" & vbNewLine & _
" INSERT IntO staff(id, name, dept, job, salary)" & vbNewLine & _
" VALUES(25, 'Pearce', 38, 'Clerk', 7217.50)," & vbNewLine & _
" (35, 'Hachey', 38, 'Mgr', 21270.00)," & vbNewLine & _
" (45, 'Wagland', 38, 'Sales', 11575.00)")
trans = conn.BegIntransaction()
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
' 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")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "DROP TRIGGER min_salary"
trans = conn.BegIntransaction()
cmd.Transaction = trans
cmd.ExecuteNonQuery()
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' TbBeforeInsertTriggerUse
' This method creates and demonstrates the use of an 'AFTER INSERT'
' trigger
Public Shared Sub TbAfterInsertTriggerUse(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" CREATE TRIGGER" & vbNewLine & _
" COMMIT" & vbNewLine & _
" INSERT" & vbNewLine & _
" ROLLBACK" & vbNewLine & _
" DROP TRIGGER" & vbNewLine & _
" 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" & vbNewLine & _
" AFTER INSERT" & vbNewLine & _
" ON staff" & vbNewLine & _
" FOR EACH ROW MODE DB2SQL" & vbNewLine & _
" BEGIN ATOMIC" & vbNewLine & _
" UPDATE staff_stats" & vbNewLine & _
" SET nbemp = nbemp + 1;" & vbNewLine & _
" END")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End try
' Insert table data using values
Try
Console.WriteLine()
Console.WriteLine( _
" Invoke the statement:" & vbNewLine & _
" INSERT IntO staff(id, name, dept, job, salary)" & vbNewLine & _
" VALUES(25, 'Pearce', 38, 'Clerk', 7217.50)," & vbNewLine & _
" (35, 'Hachey', 38, 'Mgr', 21270.00)," & vbNewLine & _
" (45, 'Wagland', 38, 'Sales', 11575.00)")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the 'staff_stats' table
trans = conn.BegIntransaction()
StaffStatsTbDrop(conn, trans)
End Sub ' TbAfterInsertTriggerUse
' This method creates and demonstrates the use of a 'BEFORE DELETE'
' trigger
Public Shared Sub TbBeforeDeleteTriggerUse(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" CREATE TRIGGER" & vbNewLine & _
" COMMIT" & vbNewLine & _
" DELETE" & vbNewLine & _
" ROLLBACK" & vbNewLine & _
" DROP TRIGGER" & vbNewLine & _
" 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" & vbNewLine & _
" NO CASCADE BEFORE DELETE" & vbNewLine & _
" ON staff" & vbNewLine & _
" REFERENCING OLD AS oldstaff" & vbNewLine & _
" FOR EACH ROW MODE DB2SQL" & vbNewLine & _
" WHEN (oldstaff.job = 'Sales')" & vbNewLine & _
" BEGIN ATOMIC" & vbNewLine & _
" SIGNAL SQLSTATE '75000' " & _
" ('Sales can not be deleted now.');" & vbNewLine & _
" END")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Delete data from the 'staff' table
Try
Console.WriteLine()
Console.WriteLine(" Invoke the statement:" & vbNewLine & _
" DELETE FROM staff WHERE id <= 50")
Dim cmd As DB2Command = conn.CreateCommand()
cmd.CommandText = "DELETE FROM staff WHERE id <= 50"
trans = conn.BegIntransaction()
cmd.Transaction = trans
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine()
Console.WriteLine( _
"**************** Expected Error ******************" & vbNewLine)
Console.WriteLine(e.Message)
Console.WriteLine( _
"**************************************************")
End Try
' 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")
Dim cmd As DB2Command = conn.CreateCommand()
trans = conn.BegIntransaction()
cmd.CommandText = "DROP TRIGGER do_not_del_sales"
cmd.Transaction = trans
cmd.ExecuteNonQuery()
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub ' TbBeforeDeleteTriggerUse
' This method creates and demonstrates the use of a 'BEFORE DELETE'
' trigger
Public Shared Sub TbBeforeUpdateTriggerUse(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" CREATE TRIGGER" & vbNewLine & _
" COMMIT" & vbNewLine & _
" UPDATE" & vbNewLine & _
" ROLLBACK" & vbNewLine & _
" DROP TRIGGER" & vbNewLine & _
" 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" & vbNewLine & _
" NO CASCADE BEFORE UPDATE OF sal" & vbNewLine & _
" ON salary_status" & vbNewLine & _
" REFERENCING NEW AS new OLD AS old" & vbNewLine & _
" FOR EACH ROW MODE DB2SQL" & vbNewLine & _
" BEGIN ATOMIC" & vbNewLine & _
" SET new.status =" & vbNewLine & _
" CASE" & vbNewLine & _
" WHEN new.sal < old.sal" & vbNewLine & _
" THEN 'Decreasing'" & vbNewLine & _
" WHEN new.sal > old.sal" & vbNewLine & _
" THEN 'Increasing'" & vbNewLine & _
" END;" & vbNewLine & _
" END")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Update data in table 'salary_status'
Try
Console.WriteLine()
Console.WriteLine( _
" Invoke the statement:" & vbNewLine & _
" UPDATE salary_status SET sal = 18000.00")
Dim cmd As DB2Command = conn.CreateCommand()
trans = conn.BegIntransaction()
cmd.CommandText = "UPDATE salary_status SET sal = 18000.00"
cmd.Transaction = trans
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the trigger
Try
Console.WriteLine()
Console.WriteLine(" DROP TRIGGER sal_status")
Dim cmd As DB2Command = conn.CreateCommand()
trans = conn.BegIntransaction()
cmd.CommandText = "DROP TRIGGER sal_status"
cmd.Transaction = trans
cmd.ExecuteNonQuery()
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' Drop salary_status table
trans = conn.BegIntransaction()
SalaryStatusTbDrop(conn, trans)
End Sub ' TbBeforeUpdateTriggerUse
' This method creates and demonstrates the use of an 'AFTER UPDATE'
' trigger
Public Shared Sub TbAfterUpdateTriggerUse(conn As DB2Connection, _
trans As DB2Transaction)
Console.WriteLine()
Console.WriteLine( _
" ----------------------------------------------------------" & _
vbNewLine & _
" USE THE SQL STATEMENTS:" & vbNewLine & _
" CREATE TRIGGER" & vbNewLine & _
" COMMIT" & vbNewLine & _
" UPDATE" & vbNewLine & _
" DROP TRIGGER" & vbNewLine & _
" 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" & vbNewLine & _
" AFTER UPDATE OF salary" & vbNewLine & _
" ON staff" & vbNewLine & _
" REFERENCING NEW AS newstaff" & vbNewLine & _
" FOR EACH ROW MODE DB2SQL" & vbNewLine & _
" BEGIN ATOMIC" & vbNewLine & _
" INSERT IntO salary_history" & vbNewLine & _
" VALUES(newstaff.name," & vbNewLine & _
" newstaff.salary," & vbNewLine & _
" CURRENT DATE);" & vbNewLine & _
" END")
Dim cmd As DB2Command = 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Update table data
Try
Console.WriteLine()
Console.WriteLine( _
" Invoke the statement:" & vbNewLine & _
" UPDATE staff SET salary = 20000.00 WHERE name = 'Sanders'")
Dim cmd As DB2Command = 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:" & vbNewLine & _
" 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:" & vbNewLine & _
" 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:" & vbNewLine & _
" 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:" & vbNewLine & _
" UPDATE staff SET salary = 21000.00 WHERE name = 'Hanes'")
cmd.CommandText = _
"UPDATE staff SET salary = 21000.00 WHERE name = 'Hanes'"
cmd.ExecuteNonQuery()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' 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 e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the trigger
Try
Console.WriteLine()
Console.WriteLine(" DROP TRIGGER sal_history")
Dim cmd As DB2Command = conn.CreateCommand()
trans = conn.BegIntransaction()
cmd.CommandText = "DROP TRIGGER sal_history"
cmd.Transaction = trans
cmd.ExecuteNonQuery()
trans.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' Drop the 'salary_history' table
trans = conn.BegIntransaction()
SalaryHistoryTbDrop(conn, trans)
End Sub ' TbAfterUpdateTriggerUse
End Class ' TbTrig