/**************************************************************************** ** (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.sqc ** ** SAMPLE: How to use a trigger on a table ** ** SQL STATEMENTS USED: ** DECLARE CURSOR ** SELECT ** OPEN ** FETCH ** CLOSE ** CREATE TABLE ** EXECUTE IMMEDIATE ** PREPARE ** DROP ** CREATE TRIGGER ** INSERT ** DELETE ** UPDATE ** ** ***************************************************************************** ** ** For more information on the sample programs, see the README file. ** ** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book. ** ** 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 ****************************************************************************/ #include <stdio.h> #include <stdlib.h> #include <string.h> #include <sqlenv.h> #include <sqlutil.h> #include "utilemb.h" int TbBeforeInsertTriggerUse(void); int TbAfterInsertTriggerUse(void); int TbBeforeDeleteTriggerUse(void); int TbBeforeUpdateTriggerUse(void); int TbAfterUpdateTriggerUse(void); /* support functions */ int StaffTbContentDisplay(void); int StaffStatsTbCreate(void); int StaffStatsTbContentDisplay(void); int StaffStatsTbDrop(void); int SalaryStatusTbCreate(void); int SalaryStatusTbContentDisplay(void); int SalaryStatusTbDrop(void); int SalaryHistoryTbCreate(void); int SalaryHistoryTbContentDisplay(void); int SalaryHistoryTbDrop(void); EXEC SQL BEGIN DECLARE SECTION; char dbAlias[15]; char user[15]; char pswd[15]; short id; char name[10]; short dept; char job[10]; short jobInd; short years; short yearsInd; double salary; double comm; short commInd; short nbemp; char emp_name[10]; double sal; char status[16]; char employee_name[10]; double salary_record; char change_date[15]; char strStmt[512]; EXEC SQL END DECLARE SECTION; int main(int argc, char *argv[]) { int rc = 0; char dbAlias[SQL_ALIAS_SZ + 1]; char user[USERID_SZ + 1]; char pswd[PSWD_SZ + 1]; /* check the command line arguments */ rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd); if (rc != 0) { return rc; } printf("\nTHIS SAMPLE SHOWS HOW TO USE TRIGGERS.\n"); /* connect to database */ rc = DbConn(dbAlias, user, pswd); if (rc != 0) { return rc; } rc = TbBeforeInsertTriggerUse(); rc = TbAfterInsertTriggerUse(); rc = TbBeforeDeleteTriggerUse(); rc = TbBeforeUpdateTriggerUse(); rc = TbAfterUpdateTriggerUse(); /* disconnect from the database */ rc = DbDisconn(dbAlias); if (rc != 0) { return rc; } return 0; } /* main */ int StaffTbContentDisplay(void) { struct sqlca sqlca; printf("\n Select * from staff where id <= 50\n"); printf(" ID NAME DEPT JOB YEARS SALARY COMM\n"); printf(" --- -------- ---- ----- ----- -------- --------\n"); EXEC SQL DECLARE c1 CURSOR FOR SELECT * FROM staff WHERE id <= 50; EXEC SQL OPEN c1; EMB_SQL_CHECK("cursor -- open"); EXEC SQL FETCH c1 INTO :id, :name, :dept, :job:jobInd, :years:yearsInd, :salary, :comm:commInd; EMB_SQL_CHECK("cursor -- fetch"); while (sqlca.sqlcode != 100) { printf(" %3d %-8.8s %4d", id, name, dept); if (jobInd >= 0) { printf(" %-5.5s", job); } else { printf(" -"); } if (yearsInd >= 0) { printf(" %5d", years); } else { printf(" -"); } printf(" %7.2f", salary); if (commInd >= 0) { printf(" %7.2f\n", comm); } else { printf(" -\n"); } EXEC SQL FETCH c1 INTO :id, :name, :dept, :job:jobInd, :years:yearsInd, :salary, :comm:commInd; EMB_SQL_CHECK("cursor -- fetch"); } EXEC SQL CLOSE c1; EMB_SQL_CHECK("cursor -- close"); return 0; } /* StaffTbContentDisplay */ int StaffStatsTbCreate(void) { struct sqlca sqlca; printf("\n CREATE TABLE staff_stats(nbemp SMALLINT)\n"); EXEC SQL CREATE TABLE staff_stats(nbemp SMALLINT); EMB_SQL_CHECK("table -- create"); printf("\n INSERT INTO staff_stats VALUES(SELECT COUNT(*) FROM staff)\n"); strcpy(strStmt, "INSERT INTO staff_stats VALUES(SELECT COUNT(*) FROM staff) "); EXEC SQL EXECUTE IMMEDIATE :strStmt; EMB_SQL_CHECK("statement -- execute immediate"); EXEC SQL COMMIT; EMB_SQL_CHECK("transaction -- commit"); return 0; } /* StaffStatsTbCreate */ int StaffStatsTbContentDisplay(void) { struct sqlca sqlca; printf("\n SELECT nbemp FROM staff_stats\n"); printf(" NBEMP\n"); printf(" -----\n"); strcpy(strStmt, "SELECT * FROM staff_stats"); EXEC SQL PREPARE stmt2 FROM :strStmt; EMB_SQL_CHECK("statement -- prepare"); EXEC SQL DECLARE c2 CURSOR FOR stmt2; EMB_SQL_CHECK("cursor -- declare"); EXEC SQL OPEN c2; EMB_SQL_CHECK("cursor -- open"); EXEC SQL FETCH c2 INTO :nbemp; EMB_SQL_CHECK("cursor -- fetch"); EXEC SQL CLOSE c2; printf(" %5d\n", nbemp); return 0; } /* StaffStatsTbContentDisplay */ int StaffStatsTbDrop(void) { struct sqlca sqlca; printf("\n DROP TABLE staff_stats\n"); EXEC SQL DROP TABLE staff_stats; EMB_SQL_CHECK("table -- drop"); EXEC SQL COMMIT; EMB_SQL_CHECK("transaction -- commit"); return 0; } /* StaffStatsTbDrop */ int SalaryStatusTbCreate(void) { struct sqlca sqlca; printf("\n CREATE TABLE salary_status(emp_name VARCHAR(9),"); printf("\n sal DECIMAL(7, 2),"); printf("\n status CHAR(15))\n"); EXEC SQL CREATE TABLE salary_status(emp_name VARCHAR(9), sal DECIMAL(7, 2), status CHAR(15)); EMB_SQL_CHECK("table -- create"); printf("\n INSERT INTO salary_status\n"); printf(" SELECT name, salary, 'Not Defined'\n"); printf(" FROM staff\n"); printf(" WHERE id <= 50\n"); strcpy(strStmt, "INSERT INTO salary_status " "SELECT name, salary, 'Not Defined' " "FROM staff " "WHERE id <= 50 "); EXEC SQL EXECUTE IMMEDIATE :strStmt; EMB_SQL_CHECK("statement -- execute immediate"); EXEC SQL COMMIT; EMB_SQL_CHECK("transaction -- commit"); return 0; } /* SalaryStatusTbCreate */ int SalaryStatusTbContentDisplay(void) { struct sqlca sqlca; printf("\n Select * from salary_status\n"); printf(" EMP_NAME SALARY STATUS \n"); printf(" ---------- -------- ----------------\n"); strcpy(strStmt, "SELECT * FROM salary_status"); EXEC SQL PREPARE stmt3 FROM :strStmt; EMB_SQL_CHECK("statement -- prepare"); EXEC SQL DECLARE c3 CURSOR FOR stmt3; EMB_SQL_CHECK("cursor -- declare"); EXEC SQL OPEN c3; EMB_SQL_CHECK("cursor -- open"); EXEC SQL FETCH c3 INTO :emp_name, :sal, :status; EMB_SQL_CHECK("cursor -- fetch"); while (sqlca.sqlcode != 100) { printf(" %-10s %7.2f %-15s\n", emp_name, sal, status); EXEC SQL FETCH c3 INTO :emp_name, :sal, :status; EMB_SQL_CHECK("cursor -- fetch"); } EXEC SQL CLOSE c3; return 0; } /* SalaryStatusTbContentDisplay */ int SalaryStatusTbDrop(void) { struct sqlca sqlca; printf("\n DROP TABLE salary_status\n"); EXEC SQL DROP TABLE salary_status; EMB_SQL_CHECK("table -- drop"); EXEC SQL COMMIT; EMB_SQL_CHECK("transaction -- commit"); return 0; } /* SalaryStatusTbDrop */ int SalaryHistoryTbCreate(void) { struct sqlca sqlca; printf("\n CREATE TABLE salary_history(employee_name VARCHAR(9),"); printf("\n salary_record DECIMAL(7, 2),"); printf("\n change_date DATE)\n"); EXEC SQL CREATE TABLE salary_history(employee_name VARCHAR(9), salary_record DECIMAL(7, 2), change_date DATE); EMB_SQL_CHECK("table -- create"); EXEC SQL COMMIT; EMB_SQL_CHECK("transaction -- commit"); return 0; } /* SalaryHistoryTbCreate */ int SalaryHistoryTbContentDisplay(void) { struct sqlca sqlca; printf("\n Select * from salary_history\n"); printf(" EMPLOYEE_NAME SALARY_RECORD CHANGE_DATE\n"); printf(" -------------- -------------- -----------\n"); strcpy(strStmt, "SELECT * FROM salary_history"); EXEC SQL PREPARE stmt4 FROM :strStmt; EMB_SQL_CHECK("statement -- prepare"); EXEC SQL DECLARE c4 CURSOR FOR stmt4; EMB_SQL_CHECK("cursor -- declare"); EXEC SQL OPEN c4; EMB_SQL_CHECK("cursor -- open"); EXEC SQL FETCH c4 INTO :employee_name, :salary_record, :change_date; EMB_SQL_CHECK("cursor -- fetch"); while (sqlca.sqlcode != 100) { printf(" %-14s %14.2f %-15s\n", employee_name, salary_record, change_date); EXEC SQL FETCH c4 INTO :employee_name, :salary_record, :change_date; EMB_SQL_CHECK("cursor -- fetch"); } EXEC SQL CLOSE c4; return 0; } /* SalaryHistoryTbContentDisplay */ int SalaryHistoryTbDrop(void) { struct sqlca sqlca; printf("\n DROP TABLE salary_history\n"); EXEC SQL DROP TABLE salary_history; EMB_SQL_CHECK("table -- drop"); EXEC SQL COMMIT; EMB_SQL_CHECK("transaction -- commit"); return 0; } /* SalaryHistoryTbDrop */ int TbBeforeInsertTriggerUse(void) { int rc = 0; struct sqlca sqlca; printf("\n-----------------------------------------------------------"); printf("\nUSE THE SQL STATEMENTS:\n"); printf(" CREATE TRIGGER\n"); printf(" COMMIT\n"); printf(" INSERT\n"); printf(" DROP TRIGGER\n"); printf("TO SHOW A 'BEFORE INSERT' TRIGGER.\n"); /* display initial table content */ rc = StaffTbContentDisplay(); printf("\n CREATE TRIGGER min_sal" "\n NO CASCADE BEFORE" "\n INSERT ON staff" "\n REFERENCING NEW AS newstaff" "\n FOR EACH ROW" "\n BEGIN ATOMIC" "\n SET newstaff.salary =" "\n CASE" "\n WHEN newstaff.job = 'Mgr' AND " "newstaff.salary < 17000.00" "\n THEN 17000.00" "\n WHEN newstaff.job = 'Sales' AND " "newstaff.salary < 14000.00" "\n THEN 14000.00" "\n WHEN newstaff.job = 'Clerk' AND " "newstaff.salary < 10000.00" "\n THEN 10000.00" "\n ELSE newstaff.salary" "\n END;" "\n END\n"); EXEC SQL 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; EMB_SQL_CHECK("trigger -- create"); EXEC SQL COMMIT; EMB_SQL_CHECK("create trigger -- commit"); /* insert into the table using values */ printf("\n Invoke the statement\n"); printf(" INSERT INTO staff(id, name, dept, job, salary)\n"); printf(" VALUES(25, 'Pearce', 38, 'Clerk', 7217.50),\n"); printf(" (35, 'Hachey', 38, 'Mgr', 21270.00),\n"); printf(" (45, 'Wagland', 38, 'Sales', 11575.00)\n"); EXEC SQL 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); EMB_SQL_CHECK("table -- insert using values"); /* display final content of the table */ rc = StaffTbContentDisplay(); /* rollback transaction */ printf("\n Rollback the transaction.\n"); EXEC SQL ROLLBACK; EMB_SQL_CHECK("transaction -- rollback"); printf("\n DROP TRIGGER min_sal\n"); EXEC SQL DROP TRIGGER min_sal; EMB_SQL_CHECK("trigger -- drop"); EXEC SQL COMMIT; EMB_SQL_CHECK("drop trigger -- commit"); return 0; } /* TbBeforeInsertTriggerUse */ int TbAfterInsertTriggerUse(void) { int rc = 0; struct sqlca sqlca; printf("\n-----------------------------------------------------------"); printf("\nUSE THE SQL STATEMENTS:\n"); printf(" CREATE TRIGGER\n"); printf(" COMMIT\n"); printf(" INSERT\n"); printf(" DROP TRIGGER\n"); printf("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(); strcpy(strStmt, "CREATE TRIGGER new_hire AFTER" " INSERT ON staff " " FOR EACH ROW " " BEGIN ATOMIC " " UPDATE staff_stats SET nbemp = nbemp + 1; " " END"); printf("\n CREATE TRIGGER new_hire AFTER" "\n INSERT ON staff" "\n FOR EACH ROW" "\n BEGIN ATOMIC" "\n UPDATE staff_stats SET nbemp = nbemp + 1;" "\n END\n"); EXEC SQL EXECUTE IMMEDIATE :strStmt; EMB_SQL_CHECK("trigger -- create"); EXEC SQL COMMIT; EMB_SQL_CHECK("create trigger -- commit"); /* insert into the table using values */ printf("\n Invoke the statement\n"); printf(" INSERT INTO staff(id, name, dept, job, salary)\n"); printf(" VALUES(25, 'Pearce', 38, 'Clerk', 7217.50),\n"); printf(" (35, 'Hachey', 38, 'Mgr', 21270.00),\n"); printf(" (45, 'Wagland', 38, 'Sales', 11575.00)\n"); EXEC SQL 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); EMB_SQL_CHECK("table -- insert using values"); /* display staff_stats table content */ rc = StaffStatsTbContentDisplay(); /* rollback transaction */ printf("\n Rollback the transaction.\n"); EXEC SQL ROLLBACK; EMB_SQL_CHECK("transaction -- rollback"); strcpy(strStmt, " DROP TRIGGER new_hire"); printf("\n%s\n", strStmt); EXEC SQL EXECUTE IMMEDIATE :strStmt; EMB_SQL_CHECK("trigger -- drop"); EXEC SQL COMMIT; EMB_SQL_CHECK("drop trigger -- commit"); /* drop staff_stats table */ rc = StaffStatsTbDrop(); return 0; } /* TbAfterInsertTriggerUse */ int TbBeforeDeleteTriggerUse(void) { int rc = 0; struct sqlca sqlca; char sqlstate[5 + 1]; char sqlerrmsg[70 + 1]; printf("\n-----------------------------------------------------------"); printf("\nUSE THE SQL STATEMENTS:\n"); printf(" CREATE TRIGGER\n"); printf(" COMMIT\n"); printf(" DELETE\n"); printf(" DROP TRIGGER\n"); printf("TO SHOW A 'BEFORE DELETE' TRIGGER.\n"); /* display initial content of the table */ rc = StaffTbContentDisplay(); printf("\n CREATE TRIGGER do_not_delete_sales" "\n NO CASCADE BEFORE" "\n DELETE ON staff" "\n REFERENCING OLD AS oldstaff" "\n FOR EACH ROW" "\n WHEN(oldstaff.job = 'Sales')" "\n BEGIN ATOMIC" "\n SIGNAL SQLSTATE '75000' " "('Sales can not be deleted now.');" "\n END\n"); EXEC SQL 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; EMB_SQL_CHECK("trigger -- create"); EXEC SQL COMMIT; EMB_SQL_CHECK("create trigger -- commit"); /* delete table */ printf("\n Invoke the statement\n"); printf(" DELETE FROM staff WHERE id <= 50\n"); EXEC SQL DELETE FROM staff WHERE id <= 50; if (sqlca.sqlcode == -438) { memcpy(sqlstate, sqlca.sqlstate, 5); sqlstate[5] = '\0'; memcpy(sqlerrmsg, sqlca.sqlerrmc, sqlca.sqlerrml); sqlerrmsg[sqlca.sqlerrml] = '\0'; printf(" SQL0438N %s SQLSTATE = %s\n", sqlerrmsg, sqlstate); } else { EMB_SQL_CHECK("table -- delete"); } /* display final content of the table */ rc = StaffTbContentDisplay(); /* rollback transaction */ printf("\n Rollback the transaction.\n"); EXEC SQL ROLLBACK; EMB_SQL_CHECK("transaction -- rollback"); printf("\n DROP TRIGGER do_not_delete_sales\n"); EXEC SQL DROP TRIGGER do_not_delete_sales; EMB_SQL_CHECK("trigger -- drop"); EXEC SQL COMMIT; EMB_SQL_CHECK("drop trigger -- commit"); return 0; } /* TbBeforeDeleteTriggerUse */ int TbBeforeUpdateTriggerUse(void) { int rc = 0; struct sqlca sqlca; printf("\n-----------------------------------------------------------"); printf("\nUSE THE SQL STATEMENTS:\n"); printf(" CREATE TRIGGER\n"); printf(" COMMIT\n"); printf(" UPDATE\n"); printf(" DROP TRIGGER\n"); printf("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(); strcpy(strStmt, "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 "); printf("\n CREATE TRIGGER salary_status" "\n NO CASCADE BEFORE" "\n UPDATE OF sal" "\n ON salary_status" "\n REFERENCING NEW AS new OLD AS old" "\n FOR EACH ROW" "\n BEGIN ATOMIC" "\n SET new.status =" "\n CASE" "\n WHEN new.sal < old.sal THEN 'Decreasing'" "\n WHEN new.sal > old.sal THEN 'Increasing'" "\n END;" "\n END\n"); EXEC SQL EXECUTE IMMEDIATE :strStmt; EMB_SQL_CHECK("trigger -- create"); EXEC SQL COMMIT; EMB_SQL_CHECK("create trigger -- commit"); /* update table */ printf("\n Invoke the statement\n"); printf(" UPDATE salary_status SET sal = 18000.00\n"); strcpy(strStmt, "UPDATE salary_status SET sal = 18000.00"); EXEC SQL EXECUTE IMMEDIATE :strStmt; EMB_SQL_CHECK("table -- update"); /* display salary_status table content */ rc = SalaryStatusTbContentDisplay(); /* rollback transaction */ printf("\n Rollback the transaction.\n"); EXEC SQL ROLLBACK; EMB_SQL_CHECK("transaction -- rollback"); strcpy(strStmt, " DROP TRIGGER sal_status"); printf("\n%s\n", strStmt); EXEC SQL EXECUTE IMMEDIATE :strStmt; EMB_SQL_CHECK("trigger -- drop"); EXEC SQL COMMIT; EMB_SQL_CHECK("drop trigger -- commit"); /* drop salary_status table */ rc = SalaryStatusTbDrop(); return 0; } /* TbBeforeUpdateTriggerUse */ int TbAfterUpdateTriggerUse(void) { int rc = 0; struct sqlca sqlca; printf("\n-----------------------------------------------------------"); printf("\nUSE THE SQL STATEMENTS:\n"); printf(" CREATE TRIGGER\n"); printf(" COMMIT\n"); printf(" UPDATE\n"); printf(" DROP TRIGGER\n"); printf("TO SHOW AN 'AFTER UPDATE' TRIGGER.\n"); /* create salary_history table */ rc = SalaryHistoryTbCreate(); if (rc != 0) { return rc; } /* display salary_history table content */ rc = SalaryHistoryTbContentDisplay(); strcpy(strStmt, "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 "); printf("\n CREATE TRIGGER sal_history" "\n AFTER" "\n UPDATE OF salary ON staff" "\n REFERENCING NEW AS newstaff" "\n FOR EACH ROW" "\n BEGIN ATOMIC" "\n INSERT INTO salary_history" "\n VALUES(newstaff.name, newstaff.salary, CURRENT DATE);" "\n END\n"); EXEC SQL EXECUTE IMMEDIATE :strStmt; EMB_SQL_CHECK("trigger -- create"); EXEC SQL COMMIT; EMB_SQL_CHECK("create trigger -- commit"); /* update table */ printf("\n Invoke the statement\n"); printf(" UPDATE staff SET salary = 20000.00 WHERE name = 'Sanders'\n"); EXEC SQL UPDATE staff SET salary = 20000.00 WHERE name = 'Sanders'; EMB_SQL_CHECK("table -- update"); printf("\n Invoke the statement\n"); printf(" UPDATE staff SET salary = 21000.00 WHERE name = 'Sanders'\n"); EXEC SQL UPDATE staff SET salary = 21000.00 WHERE name = 'Sanders'; EMB_SQL_CHECK("table -- update"); printf("\n Invoke the statement\n"); printf(" UPDATE staff SET salary = 23000.00 WHERE name = 'Sanders'\n"); EXEC SQL UPDATE staff SET salary = 23000.00 WHERE name = 'Sanders'; EMB_SQL_CHECK("table -- update"); printf("\n Invoke the statement\n"); printf(" UPDATE staff SET salary = 20000.00 WHERE name = 'Hanes'\n"); EXEC SQL UPDATE staff SET salary = 20000.00 WHERE name = 'Hanes'; EMB_SQL_CHECK("table -- update"); printf("\n Invoke the statement\n"); printf(" UPDATE staff SET salary = 21000.00 WHERE name = 'Hanes'\n"); EXEC SQL UPDATE staff SET salary = 21000.00 WHERE name = 'Hanes'; EMB_SQL_CHECK("table -- update") /* display salary_history table content */ rc = SalaryHistoryTbContentDisplay(); /* rollback transaction */ printf("\n Rollback the transaction.\n"); EXEC SQL ROLLBACK; EMB_SQL_CHECK("transaction -- rollback"); strcpy(strStmt, " DROP TRIGGER sal_history"); printf("\n%s\n", strStmt); EXEC SQL EXECUTE IMMEDIATE :strStmt; EMB_SQL_CHECK("trigger -- drop"); EXEC SQL COMMIT; EMB_SQL_CHECK("drop trigger -- commit"); /* drop salary_history table */ rc = SalaryHistoryTbDrop(); return 0; } /* TbAfterUpdateTriggerUse */