#!/usr/bin/perl ############################################################################# # (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.pl # # SAMPLE: How to use a trigger on a table # # SQL STATEMENTS USED: # SELECT # CREATE TABLE # DROP # CREATE TRIGGER # INSERT # DELETE # UPDATE # # ############################################################################# # # For more information on the sample programs, see the README file. # # For information on developing perl applications, see the Application # Development Guide. # # For information on using SQL statements, see the SQL Reference. # # For the latest information on programming, building, and running DB2 # applications, visit the DB2 Information Center: # http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp ############################################################################# select STDERR; $|=1; select STDOUT; $|=1; use strict; use warnings; use DBI; # access the module for DB2 Sample Utility functions use DB2SampUtil; # check and parse the command line arguments # call the subroutine CmdLineArgChk from DB2SampUtil.pm my ($database, $user, $password) = CmdLineArgChk(@ARGV); # declare return code, statement handler, database handler my ($rc, $sth, $dbh); print "\nTHIS SAMPLE SHOWS HOW TO USE TRIGGERS.\n"; # connect to the database print "\n Connecting to database...\n"; $dbh = DBI->connect($database, $user, $password, {AutoCommit =>0}) || die "Can't connect to $database: $DBI::errstr"; print " Connected to database.\n"; # call the TbBeforeInsertTriggerUse subroutine TbBeforeInsertTriggerUse(); # call the TbAfterInsertTriggerUse subroutine TbAfterInsertTriggerUse(); # call the TbBeforeDeleteTriggerUse subroutine TbBeforeDeleteTriggerUse(); # call the TbBeforeUpdateTriggerUse subroutine TbBeforeUpdateTriggerUse(); # call the TbAfterUpdateTriggerUse subroutine TbAfterUpdateTriggerUse(); # disconnect from the database print "\n Disconnecting from database..."; $dbh->disconnect || die "Can't disconnect from database: $DBI::errstr"; print "\n Disconnected from database.\n"; ############################################################################# # Description: The StaffTbContentDisplay subroutine displays the contents of # the 'staff' table. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub StaffTbContentDisplay { my ($id, $name, $dept, $job, $years, $salary, $comm) = "0" ; print "\n Select * from staff where id <= 50\n"; print " ID NAME DEPT JOB YEARS SALARY COMM\n"; print " --- -------- ---- ----- ----- -------- --------\n"; my $sql = qq(SELECT * FROM staff WHERE id <= 50); # prepare the sql statement $sth = $dbh->prepare($sql); # execute the sql statement $sth->execute; while (($id, $name, $dept, $job, $years, $salary, $comm) = $sth->fetchrow_array) { printf " %3d %-8.8s %4d", $id, $name, $dept; if(defined $job) { printf " %-5.5s", $job; } else { print " -"; } if(defined $years) { printf " %5d", $years; } else { print " -"; } printf " %7.2f", $salary; if(defined $comm) { printf " %7.2f\n", $comm; } else { print " -\n"; } } # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # StaffTbContentDisplay ############################################################################# # Description: The StaffStatsTbCreate subroutine creates the table # 'staff_stats' for the TbAfterInsertTriggerUse subroutine. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub StaffStatsTbCreate { print "\n CREATE TABLE staff_stats(nbemp SMALLINT)\n"; my $sql = qq(CREATE TABLE staff_stats(nbemp SMALLINT)); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; print "\n INSERT INTO staff_stats VALUES(SELECT COUNT(*) FROM staff)\n"; $sql = qq(INSERT INTO staff_stats VALUES(SELECT COUNT(*) FROM staff)); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # StaffStatsTbCreate ############################################################################# # Description: The StaffStatsTbContentDisplay subroutine displays the # contents of the 'staff_stats' table. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub StaffStatsTbContentDisplay { print "\n SELECT nbemp FROM staff_stats\n"; print " NBEMP\n"; print " -----\n"; my $sql = qq(SELECT * FROM staff_stats); # prepare the sql statement $sth = $dbh->prepare($sql); # execute the statement $sth->execute; my ($nbemp) = $sth->fetchrow; printf " %5d\n", $nbemp; # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # StaffStatsTbContentDisplay ############################################################################# # Description: The StaffStatsTbDrop subroutine drops the table 'staff_stats' # that is used by the TbAfterInsertTriggerUse subroutine. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub StaffStatsTbDrop { print "\n DROP TABLE staff_stats\n"; my $sql = qq(DROP TABLE staff_stats); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # StaffStatsTbDrop ############################################################################# # Description: The SalaryStatusTbCreate subroutine creates the table # 'salary_status' for the TbBeforeUpdateTriggerUse subroutine. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub SalaryStatusTbCreate { print "\n CREATE TABLE salary_status(emp_name VARCHAR(9),"; print "\n sal DECIMAL(7, 2),"; print "\n status CHAR(15))\n"; my $sql = qq(CREATE TABLE salary_status(emp_name VARCHAR(9), sal DECIMAL(7, 2), status CHAR(15))); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); print "\n INSERT INTO salary_status\n"; print " SELECT name, salary, 'Not Defined'\n"; print " FROM staff\n"; print " WHERE id <= 50\n"; $sql = qq(INSERT INTO salary_status SELECT name, salary, 'Not Defined' FROM staff WHERE id <= 50); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # SalaryStatusTbCreate ############################################################################# # Description: The SalaryStatusTbContentDisplay subroutine displays the # contents of the 'salary_status' table. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub SalaryStatusTbContentDisplay { my ($emp_name, $sal, $status); print "\n Select * from salary_status\n"; print " EMP_NAME SALARY STATUS \n"; print " ---------- -------- ----------------\n"; my $sql = qq(SELECT * FROM salary_status); # prepare the sql statement $sth = $dbh->prepare($sql); # execute the statement $sth->execute; while(($emp_name, $sal, $status) = $sth->fetchrow_array) { printf " %-10s %7.2f %-15s\n", $emp_name, $sal, $status; } # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # SalaryStatusTbContentDisplay ############################################################################# # Description: The SalaryStatusTbDrop subroutine drops the table # 'salary_status' that is used by the TbBeforeUpdateTriggerUse # subroutine. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub SalaryStatusTbDrop { print "\n DROP TABLE salary_status\n"; my $sql = qq(DROP TABLE salary_status); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # SalaryStatusTbDrop ############################################################################# # Description: The SalaryHistoryTbCreate subroutine creates the table 'salary_ # history' for the TbAfterUpdateTriggerUse subroutine. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub SalaryHistoryTbCreate { print "\n CREATE TABLE salary_history(employee_name VARCHAR(9),"; print "\n salary_record DECIMAL(7, 2),"; print "\n change_date DATE)\n"; my $sql = qq(CREATE TABLE salary_history(employee_name VARCHAR(9), salary_record DECIMAL(7, 2), change_date DATE)); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # SalaryHistoryTbCreate ############################################################################# # Description: The SalaryHistoryTbContentDisplay subroutine displays the # contents of the 'salary_history' table. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub SalaryHistoryTbContentDisplay { my ($employee_name, $salary_record, $change_date); print "\n Select * from salary_history\n"; print " EMPLOYEE_NAME SALARY_RECORD CHANGE_DATE\n"; print " -------------- -------------- -----------\n"; my $sql = qq(SELECT * FROM salary_history); # prepare the sql statement $sth = $dbh->prepare($sql); # execute the sql statement $sth->execute; while(($employee_name, $salary_record, $change_date) = $sth->fetchrow_array) { printf " %-14s %14.2f %-15s\n", $employee_name, $salary_record, $change_date; } # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # SalaryHistoryTbContentDisplay ############################################################################# # Description: The SalaryHistoryTbDrop subroutine drops the table # 'salary_history' that is used by the TbAfterUpdateTriggerUse # subroutine. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub SalaryHistoryTbDrop { print "\n DROP TABLE salary_history\n"; my $sql = qq(DROP TABLE salary_history); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # SalaryHistoryTbDrop ############################################################################# # Description: The TbBeforeInsertTriggerUse subroutine illustrates 'BEFORE # INSERT' trigger. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub TbBeforeInsertTriggerUse { print "\n-----------------------------------------------------------"; print "\nUSE THE SQL STATEMENTS:\n"; print " CREATE TRIGGER\n"; print " COMMIT\n"; print " INSERT\n"; print " DROP TRIGGER\n"; print "TO SHOW A 'BEFORE INSERT' TRIGGER.\n"; # display initial table content $rc = StaffTbContentDisplay(); print "\n CREATE TRIGGER min_sal"; print "\n NO CASCADE BEFORE"; print "\n INSERT ON staff"; print "\n REFERENCING NEW AS newstaff"; print "\n FOR EACH ROW"; print "\n BEGIN ATOMIC"; print "\n SET newstaff.salary ="; print "\n CASE"; print "\n WHEN newstaff.job = 'Mgr' AND "; print "newstaff.salary < 17000.00"; print "\n THEN 17000.00"; print "\n WHEN newstaff.job = 'Sales' AND "; print "newstaff.salary < 14000.00"; print "\n THEN 14000.00"; print "\n WHEN newstaff.job = 'Clerk' AND "; print "newstaff.salary < 10000.00"; print "\n THEN 10000.00"; print "\n ELSE newstaff.salary"; print "\n END;"; print "\n END\n"; my $sql = qq(CREATE TRIGGER min_sal NO CASCADE BEFORE INSERT ON staff REFERENCING NEW AS newstaff FOR EACH ROW 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); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # insert into the table using values print "\n Invoke the statement\n"; print " INSERT INTO staff(id, name, dept, job, salary)\n"; print " VALUES(25, 'Pearce', 38, 'Clerk', 7217.50),\n"; print " (35, 'Hachey', 38, 'Mgr', 21270.00),\n"; print " (45, 'Wagland', 38, 'Sales', 11575.00)\n"; $sql = qq(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)); # execute the sql statement $sth = $dbh->do($sql); # display final content of the table $rc = StaffTbContentDisplay(); # rollback transaction print "\n Rollback the transaction.\n"; $rc = $dbh->rollback; print "\n DROP TRIGGER min_sal\n"; $sql = qq(DROP TRIGGER min_sal); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # TbBeforeInsertTriggerUse ############################################################################# # Description: The TbAfterInsertTriggerUse subroutine illustrates 'AFTER # INSERT' trigger. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub TbAfterInsertTriggerUse { print "\n-----------------------------------------------------------"; print "\nUSE THE SQL STATEMENTS:\n"; print " CREATE TRIGGER\n"; print " COMMIT\n"; print " INSERT\n"; print " DROP TRIGGER\n"; print "TO SHOW AN 'AFTER INSERT' TRIGGER.\n"; # create staff_stats table $rc = StaffStatsTbCreate(); if($rc != 0) { return $rc; } # display staff_stats table content $rc = StaffStatsTbContentDisplay(); print "\n CREATE TRIGGER new_hire AFTER"; print "\n INSERT ON staff"; print "\n FOR EACH ROW"; print "\n BEGIN ATOMIC"; print "\n UPDATE staff_stats SET nbemp = nbemp + 1;"; print "\n END\n"; my $sql = qq(CREATE TRIGGER new_hire AFTER INSERT ON staff FOR EACH ROW BEGIN ATOMIC UPDATE staff_stats SET nbemp = nbemp + 1; END); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # insert into the table using values print "\n Invoke the statement\n"; print " INSERT INTO staff(id, name, dept, job, salary)\n"; print " VALUES(25, 'Pearce', 38, 'Clerk', 7217.50),\n"; print " (35, 'Hachey', 38, 'Mgr', 21270.00),\n"; print " (45, 'Wagland', 38, 'Sales', 11575.00)\n"; $sql = qq(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)); # execute the sql statement $sth = $dbh->do($sql); # display staff_stats table content $rc = StaffStatsTbContentDisplay(); # rollback transaction print "\n Rollback the transaction.\n"; $rc = $dbh->rollback; print "\n DROP TRIGGER new_hire\n"; $sql = qq(DROP TRIGGER new_hire); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # drop staff_stats table $rc = StaffStatsTbDrop(); # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # TbAfterInsertTriggerUse ############################################################################# # Description: The TbBeforeDeleteTriggerUse subroutine illustrates 'BEFORE # DELETE' trigger. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub TbBeforeDeleteTriggerUse { print "\n-----------------------------------------------------------"; print "\nUSE THE SQL STATEMENTS:\n"; print " CREATE TRIGGER\n"; print " COMMIT\n"; print " DELETE\n"; print " DROP TRIGGER\n"; print "TO SHOW A 'BEFORE DELETE' TRIGGER.\n"; # display initial content of the table $rc = StaffTbContentDisplay(); print "\n CREATE TRIGGER do_not_delete_sales"; print "\n NO CASCADE BEFORE"; print "\n DELETE ON staff"; print "\n REFERENCING OLD AS oldstaff"; print "\n FOR EACH ROW"; print "\n WHEN(oldstaff.job = 'Sales')"; print "\n BEGIN ATOMIC"; print "\n SIGNAL SQLSTATE '75000' "; print "('Sales can not be deleted now.');"; print "\n END\n"; my $sql = qq(CREATE TRIGGER do_not_delete_sales NO CASCADE BEFORE DELETE ON staff REFERENCING OLD AS oldstaff FOR EACH ROW WHEN(oldstaff.job = 'Sales') BEGIN ATOMIC SIGNAL SQLSTATE '75000' ('Sales can not be deleted now.'); END); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # delete table print "\n Invoke the statement\n"; print " DELETE FROM staff WHERE id <= 50\n"; $sql = qq(DELETE FROM staff WHERE id <= 50); # disable DBI error message printing $dbh->{PrintError} = 0; # execute the sql statement $sth = $dbh->do($sql); if($DBI::err == -438) { print " SQL0438N Sales can not be deleted now. SQLSTATE = $DBI::state\n"; } # enable DBI error message printing $dbh->{PrintError} = 1; # display final content of the table $rc = StaffTbContentDisplay(); # rollback transaction print "\n Rollback the transaction.\n"; $rc = $dbh->rollback; print "\n DROP TRIGGER do_not_delete_sales\n"; $sql = qq(drop trigger do_not_delete_sales); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # TbBeforeDeleteTriggerUse ############################################################################# # Description: The TbBeforeUpdateTriggerUse subroutine illustrates 'BEFORE # UPDATE' trigger. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub TbBeforeUpdateTriggerUse { print "\n-----------------------------------------------------------"; print "\nUSE THE SQL STATEMENTS:\n"; print " CREATE TRIGGER\n"; print " COMMIT\n"; print " UPDATE\n"; print " DROP TRIGGER\n"; print "TO SHOW A 'BEFORE UPDATE' TRIGGER.\n"; # create salary_status table $rc = SalaryStatusTbCreate(); if ($rc != 0) { return $rc; } # display salary_status table content $rc = SalaryStatusTbContentDisplay(); my $sql = qq(CREATE TRIGGER sal_status NO CASCADE BEFORE UPDATE OF sal ON salary_status REFERENCING NEW AS new OLD AS old FOR EACH ROW BEGIN ATOMIC SET new.status = CASE WHEN new.sal < old.sal THEN 'Decreasing' WHEN new.sal > old.sal THEN 'Increasing' END; END); print "\n CREATE TRIGGER salary_status"; print "\n NO CASCADE BEFORE"; print "\n UPDATE OF sal"; print "\n ON salary_status"; print "\n REFERENCING NEW AS new OLD AS old"; print "\n FOR EACH ROW"; print "\n BEGIN ATOMIC"; print "\n SET new.status ="; print "\n CASE"; print "\n WHEN new.sal < old.sal THEN 'Decreasing'"; print "\n WHEN new.sal > old.sal THEN 'Increasing'"; print "\n END;"; print "\n END\n"; # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # update table print "\n Invoke the statement\n"; print " UPDATE salary_status SET sal = 18000.00\n"; $sql = qq(UPDATE salary_status SET sal = 18000.00); # execute the sql statement $sth = $dbh->do($sql); # display salary_status table content $rc = SalaryStatusTbContentDisplay(); # rollback transaction print "\n Rollback the transaction.\n"; $rc = $dbh->rollback; print "\n DROP TRIGGER sal_status\n"; $sql = qq(drop trigger sal_status); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # drop salary_status table $rc = SalaryStatusTbDrop(); # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # TbBeforeUpdateTriggerUse ############################################################################# # Description: The TbAfterUpdateTriggerUse subroutine illustrates 'AFTER # UPDATE' trigger. # Input : None # Output : Returns 0 on success, exits otherwise. ############################################################################# sub TbAfterUpdateTriggerUse { print "\n-----------------------------------------------------------"; print "\nUSE THE SQL STATEMENTS:\n"; print " CREATE TRIGGER\n"; print " COMMIT\n"; print " UPDATE\n"; print " DROP TRIGGER\n"; print "TO SHOW AN 'AFTER UPDATE' TRIGGER.\n"; # create salary_history table $rc = SalaryHistoryTbCreate(); # display salary_history table content $rc = SalaryHistoryTbContentDisplay(); my $sql = qq(CREATE TRIGGER sal_history AFTER UPDATE OF salary ON staff REFERENCING NEW AS newstaff FOR EACH ROW BEGIN ATOMIC INSERT INTO salary_history VALUES(newstaff.name, newstaff.salary, CURRENT DATE); END); print "\n CREATE TRIGGER sal_history"; print "\n AFTER"; print "\n UPDATE OF salary ON staff"; print "\n REFERENCING NEW AS newstaff"; print "\n FOR EACH ROW"; print "\n BEGIN ATOMIC"; print "\n INSERT INTO salary_history"; print "\n VALUES(newstaff.name, newstaff.salary, CURRENT DATE);"; print "\n END\n"; # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # update table print "\n Invoke the statement\n"; print " UPDATE staff SET salary = 20000.00 WHERE name = 'Sanders'\n"; $sql = qq(UPDATE staff SET salary = 20000.00 WHERE name = 'Sanders'); # execute the sql statement $sth = $dbh->do($sql); print "\n Invoke the statement\n"; print " UPDATE staff SET salary = 21000.00 WHERE name = 'Sanders'\n"; $sql = qq(UPDATE staff SET salary = 21000.00 WHERE name = 'Sanders'); # execute the sql statement $sth = $dbh->do($sql); print "\n Invoke the statement\n"; print " UPDATE staff SET salary = 23000.00 WHERE name = 'Sanders'\n"; $sql = qq(UPDATE staff SET salary = 23000.00 WHERE name = 'Sanders'); # execute the sql statement $sth = $dbh->do($sql); print "\n Invoke the statement\n"; print " UPDATE staff SET salary = 20000.00 WHERE name = 'Hanes'\n"; $sql = qq(UPDATE staff SET salary = 20000.00 WHERE name = 'Hanes'); # execute the sql statement $sth = $dbh->do($sql); print "\n Invoke the statement\n"; print " UPDATE staff SET salary = 21000.00 WHERE name = 'Hanes'\n"; $sql = qq(UPDATE staff SET salary = 21000.00 WHERE name = 'Hanes'); # execute the sql statement $sth = $dbh->do($sql); # display salary_history table content $rc = SalaryHistoryTbContentDisplay(); # rollback transaction print "\n Rollback the transaction.\n"; $rc = $dbh->rollback; print "\n DROP TRIGGER sal_history\n"; $sql = qq(drop trigger sal_history); # call PrepareExecuteSql subroutine defined in DB2SampUtil.pm $sth = PrepareExecuteSql($dbh, $sql); # commit the transaction $rc = $dbh->commit; # drop salary_history table $rc = SalaryHistoryTbDrop(); # no more data to be fetched from statement handle $rc = $sth->finish; return 0; } # TbAfterUpdateTriggerUse