Creates triggers for a specified table. A trigger defines the
actions to occur before or after the table is updated (by a write
operation such as an INSERT, DELETE, or UPDATE statement). The
trigger is associated with a single table, takes effect at a
specific time for a particular type of update, and causes one or
more triggered actions to be performed. If the trigger specifies
multiple actions, each action is performed in the order in which
it appears within the trigger definition.
With triggers, you can define useful actions such as:
o Cascading deletes
Deleting a row from one table causes additional rows to be
deleted from other tables that are related to the first table
by key values.
o Cascading updates
Updating a row in one table causes additional rows to be
updated in other tables that are related to the first table
by key values. These updates are commonly limited to the key
fields themselves.
o Summation updates
Updating a row from one table causes a value in a row of
another table to be updated by being increased or decreased.
o Hidden deletes
Causing rows to be deleted from a table by moving them to a
parallel table that is not otherwise used by the database.
NOTE
Combinations of table-specific constraints and appropriately
defined triggers, by themselves, are not sufficient to
guarantee that database integrity is preserved when the
database is updated. If integrity is to be preserved,
table-specific constraints and triggers must be used in
conjunction with a common set of update procedures that
ensure completely reproducible and consistent retrieval and
update strategies.
The CREATE TRIGGER statement adds the trigger definition to the
physical database.
A triggered action consists of an optional predicate and some
triggered statements. If specified, the predicate must evaluate
to true for the triggered statements in the action to execute.
Each triggered statement is executed in the order in which it
appears within the triggered action clause.
The triggered statement can be:
o A DELETE statement
o An UPDATE statement
o An INSERT statement
o A CALL statement
o A SIGNAL statement
o A TRACE statement
o An ERROR statement
1 – Environment
You can use the CREATE TRIGGER statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mCREATE[m[1m [1;4mTRIGGER[m[1m qq> <trigger-name> qqqqqqk [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk [m
[1m mq> [1;4mSTORED[m[1m [1;4mNAME[m[1m IS <stored-name> qqqj x [m
[1mlqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqj [m
[1mmwq> [1;4mBEFORE[m[1m qwqwq> [1;4mINSERT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk [m
[1m mq> [1;4mAFTER[m[1m qqj tq> [1;4mDELETE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x [m
[1m mq> [1;4mUPDATE[m[1m qqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqqj x [m
[1m mqq> [1;4mOF[m[1m qw> <column-name> wj x [m
[1m mqqq , <qqqqqqqqqj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmq> [1;4mON[m[1m <table-name> wqqqqqqqqqqqqqqqqqqqqqqqwwq> triggered-action qw> [m
[1m mq> referencing-clause qjmqqqqqqqqqq<qqqqqqqqqqj [m
[1m [m
(B)0[m[1mreferencing-clause = [m
[1m [m
[1;4mREFERENCING[m[1m qwwq> [1;4mOLD[m[1m AS qq> <old-correlation-name> qwwq> [m
[1m xmq> [1;4mNEW[m[1m AS qq> <new-correlation-name> qjx [m
[1m mqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mtriggered-action = [m
[1m [m
[1mqqwqqqqqqqqqqqqqqqqqqqqqwq> ( qw> triggered-statement qwq> ) qk [m
[1m mq> [1;4mWHEN[m[1m (predicate) qj tqqqqqqqqqqq , <qqqqqqqqu x [m
[1mmqqqqqqqqqqq[m [1m;[m [1m<qqqqqqqqj[m [1mx[m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mqwqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq> [m
[1m tq> [1;4mFOR[m[1m [1;4mEACH[m[1m [1;4mROW[m[1m qqqqqqqqu [m
[1m mq> [1;4mFOR[m[1m [1;4mEACH[m[1m [1;4mSTATEMENT[m[1m qqj [m
(B)0[m[1mtriggered-statement = [m
[1m [m
[1mqqwq> call-statement qqqwqq> [m
[1m tq> delete-statement qu [m
[1m tq> [1;4mERROR[m [1mqqqqqqqqqqqqu [m
[1mtq> insert-statement qu[m
[1mtq> signal-statement qu[m
[1mtq> trace-statement qqu[m
[1m mq> update-statement qj [m [1m [m
3 – Arguments
3.1 – call-statement
Specifies the stored procedure to invoke. You can only call
procedures with IN parameters. Operations on the triggering table
are not permitted due to possible side effects and recursive
calls.
3.2 – column-name
The name of a column within the specified table to be checked for
deletion, modification, or insertion. Use this argument only with
UPDATE triggers.
3.3 – delete-statement
Specifies the row of a table that you want to delete. If you
specify CURRENT OF cursor-name with the WHERE clause of the
DELETE statement, you receive an error message because the cursor
is not visible to the CREATE TRIGGER statement.
3.4 – ERROR
Provides the following message:
RDMS-E-TRIG_ERROR, Trigger 'trigger_name' forced an error.
A triggered ERROR statement cancels the DELETE, UPDATE, or INSERT
statement that invoked the trigger.
3.5 – FOR_EACH
Syntax options:
FOR EACH ROW | FOR EACH STATEMENT
Specifies whether the triggered action is evaluated once per
triggering statement, or for each row of the subject table that
is affected by the triggering statement.
If you specify FOR EACH STATEMENT, then the triggered action
is evaluated only once, and row values are not available to the
triggered action.
The FOR EACH STATEMENT clause is the default.
3.6 – insert-statement
Specifies the new row or rows you want to add to a table.
3.7 – old-correlation-name
A temporary name used to refer to the row values as they existed
before an UPDATE operation occurred. If you do not specify the
FOR EACH ROW clause, this correlation name cannot be referred to
in the triggered statement.
3.8 – new-correlation-name
A temporary name used to refer to the new row values to be
applied by the UPDATE operation. If you do not specify the FOR
EACH ROW clause, this correlation name cannot be referred to in
the triggered statement.
3.9 – referencing-clause
Lets you specify whether you want to refer to the row values as
they existed before an UPDATE operation occurred or the new row
values after they are applied by the UPDATE operation. Do not use
this clause with INSERT or DELETE operations.
You can specify each option (OLD AS old-correlation-name or NEW
AS new-correlation-name) only once in the referencing clause.
3.10 – signal-statement
Specifies that the signaled SQLSTATE status parameter is to be
passed back to the application or SQL interface and that the
current routine and all calling routines are to be terminated.
This provides a more complete error mechanism than is provided by
the ERROR clause.
3.11 – STORED_NAME_IS
Specifies a name that Oracle Rdb uses to access a trigger created
in a multischema database. The stored name allows you to access
multischema definitions using interfaces, such as Oracle RMU,
the Oracle Rdb management utility, that do not recognize multiple
schemas in one database. You cannot specify a stored name for a
trigger in a database that does not allow multiple schemas. For
more information on stored names, see the User_Supplied_Names
HELP topic.
3.12 – table-name
The name of the table for which this trigger is defined.
3.13 – trace-statement
Allows applications to add triggers to log information when trace
logging is active.
3.14 – triggered-action
Consists of an optional predicate, some triggered statements, and
an optional frequency clause. If specified, the predicate must
evaluate to true for the triggered statements in the triggered
action clause to execute. Each triggered statement is executed in
the order in which it appears within the triggered action clause.
3.15 – triggered-statement
Updates the database or generates an error message.
3.16 – update-statement
Specifies the row of a table that you want to modify. If you
specify CURRENT OF cursor-name with the WHERE clause of the
UPDATE statement, you receive an error message because the cursor
is not visible to the CREATE TRIGGER statement.
3.17 – WHEN predicate
Describes the optional condition that must be satisfied before
the associated triggered statements are executed. This predicate
cannot refer to any host language variable.
To avoid ambiguity between columns and external function
callouts, use parentheses around the predicate in the WHEN
clause.
4 – Examples
Example 1: Defining a cascading delete trigger
The following SQL procedure shows a trigger from the sample
personnel database that deletes rows in several tables before
deleting a row in the EMPLOYEES table. Each associated employee
row (from the tables that have foreign keys referring to the
primary key in the employee row) is deleted. The employee
identification number being deleted (00164) belongs to an
employee who is also a manager; therefore, the MANAGER_ID column
in the DEPARTMENTS table is set to null, as specified by the
trigger.
SQL> SET TRANSACTION READ WRITE;
SQL> --
SQL> -- Display the EMPLOYEE_ID_CASCADE_DELETE trigger
SQL> -- in the sample database:
SQL> --
SQL> SHOW TRIGGER EMPLOYEE_ID_CASCADE_DELETE
EMPLOYEE_ID_CASCADE_DELETE
Source:
EMPLOYEE_ID_CASCADE_DELETE
BEFORE DELETE ON EMPLOYEES
(DELETE FROM DEGREES D WHERE D.EMPLOYEE_ID =
EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
(DELETE FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID =
EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
(DELETE FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID =
EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
! Also, if an employee is terminated and that employee
! is the manager of a department, set the manager_id
! null for that department.
(UPDATE DEPARTMENTS D SET D.MANAGER_ID = NULL
WHERE D.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
SQL> --
SQL> -- The EMPLOYEES table has a value of '00164'
SQL> -- in the EMPLOYEE_ID column:
SQL> --
SQL> SELECT * FROM EMPLOYEES E WHERE E.EMPLOYEE_ID = '00164';
EMPLOYEE_ID LAST_NAME FIRST_NAME MIDDLE_INITIAL
ADDRESS_DATA_1 ADDRESS_DATA_2 CITY
STATE POSTAL_CODE SEX BIRTHDAY STATUS_CODE
00164 Toliver Alvin A
146 Parnell Place Chocorua
NH 03817 M 28-Mar-1947 1
1 row selected
SQL> --
SQL> --
SQL> -- The DEGREES table has two values of '00164'
SQL> -- in the EMPLOYEE_ID column:
SQL> --
SQL> SELECT * FROM DEGREES D WHERE D.EMPLOYEE_ID = '00164';
EMPLOYEE_ID COLLEGE_CODE YEAR_GIVEN DEGREE DEGREE_FIELD
00164 PRDU 1973 MA Applied Math
00164 PRDU 1982 PhD Statistics
2 rows selected
SQL> --
SQL> --
SQL> -- The JOB_HISTORY table has the value of '00164' in
SQL> -- several rows in the EMPLOYEE_ID column:
SQL> --
SQL> SELECT * FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID = '00164';
EMPLOYEE_ID JOB_CODE JOB_START JOB_END DEPARTMENT_CODE
SUPERVISOR_ID
00164 DMGR 21-Sep-1981 NULL MBMN
00228
00164 SPGM 5-Jul-1980 20-Sep-1981 MCBM
00164
2 rows selected
SQL> --
SQL> --
SQL> -- The SALARY_HISTORY table has a value of '00164'
SQL> -- in several rows in the EMPLOYEE_ID column:
SQL> --
SQL> SELECT * FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID = '00164';
EMPLOYEE_ID SALARY_AMOUNT SALARY_START SALARY_END
00164 $26,291.00 5-Jul-1980 2-Mar-1981
00164 $51,712.00 14-Jan-1983 NULL
00164 $26,291.00 2-Mar-1981 21-Sep-1981
00164 $50,000.00 21-Sep-1981 14-Jan-1983
4 rows selected
SQL> --
SQL> --
SQL> -- The DEPARTMENTS table has a value of '00164'
SQL> -- in the MANAGER_ID column:
SQL> --
SQL> SELECT * FROM DEPARTMENTS D WHERE D.MANAGER_ID = '00164';
DEPARTMENT_CODE DEPARTMENT_NAME MANAGER_ID
BUDGET_PROJECTED BUDGET_ACTUAL
MBMN Board Manufacturing North 00164
NULL NULL
1 row selected
SQL> --
SQL> --
SQL> -- Test the trigger by deleting the row with a value of '00164'
SQL> -- in the EMPLOYEE_ID column from the EMPLOYEES table:
SQL> --
SQL> DELETE FROM EMPLOYEES E WHERE E.EMPLOYEE_ID = '00164';
1 row deleted
SQL> --
SQL> -- The row with a value of '00164' in the EMPLOYEE_ID column
SQL> -- was deleted from the EMPLOYEES table:
SQL> --
SQL> SELECT * FROM EMPLOYEES E WHERE E.EMPLOYEE_ID = '00164';
0 rows selected
SQL> --
SQL> -- The rows with a value of '00164' in the EMPLOYEE_ID column
SQL> -- were deleted from the DEGREES table:
SQL> --
SQL> SELECT * FROM DEGREES D WHERE D.EMPLOYEE_ID = '00164';
0 rows selected
SQL> --
SQL> -- The rows with a value of '00164' in the EMPLOYEE_ID
SQL> -- column were deleted from the JOB_HISTORY table:
SQL> --
SQL> SELECT * FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID = '00164';
0 rows selected
SQL> --
SQL> -- The rows with a value of '00164' in the EMPLOYEE_ID
SQL> -- column were deleted from the SALARY_HISTORY table:
SQL> --
SQL> SELECT * FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID = '00164';
0 rows selected
SQL> --
SQL> -- The value of '00164' in the MANAGER_ID column was set to null
SQL> -- in the DEPARTMENTS table:
SQL> --
SQL> SELECT * FROM DEPARTMENTS D WHERE D.DEPARTMENT_CODE = 'MBMN';
DEPARTMENT_CODE DEPARTMENT_NAME MANAGER_ID
BUDGET_PROJECTED BUDGET_ACTUAL
MBMN Board Manufacturing North NULL
NULL NULL
1 row selected
SQL> --
SQL> ROLLBACK;
Example 2: Defining a trigger that performs an update
Before the STATUS_CODE column in WORK_STATUS table is updated,
the STATUS_CODE_CASCADE_UPDATE trigger in the following SQL
procedure updates the associated rows in the EMPLOYEES table. The
REFERENCING clause specifies OLD_WORK_STATUS as the correlation
name for the values in the WORK_STATUS table before the UPDATE
statement executes, and NEW_WORK_STATUS as the correlation
name for the values in the WORK_STATUS table after the UPDATE
statement executes.
SQL> -- Display the STATUS_CODE_CASCADE_UPDATE trigger in
SQL> -- the sample database:
SQL> --
SQL> SHOW TRIGGER STATUS_CODE_CASCADE_UPDATE
STATUS_CODE_CASCADE_UPDATE
Source:
STATUS_CODE_CASCADE_UPDATE
BEFORE UPDATE OF STATUS_CODE ON WORK_STATUS
REFERENCING OLD AS OLD_WORK_STATUS
NEW AS NEW_WORK_STATUS
(UPDATE EMPLOYEES E
SET E.STATUS_CODE = NEW_WORK_STATUS.STATUS_CODE
WHERE E.STATUS_CODE = OLD_WORK_STATUS.STATUS_CODE)
FOR EACH ROW
SQL> --
SQL> -- Change the STATUS_CODE column with a value of 2 to a value of 3:
SQL> --
SQL> UPDATE WORK_STATUS WS SET STATUS_CODE="3" WHERE STATUS_CODE="2";
1 row updated
SQL> --
SQL> -- The trigger changes any STATUS_CODE column in the EMPLOYEES table
SQL> -- with a value of 2 to a value of 3. Therefore, no rows are
SQL> -- selected for the first query that follows, but several are selected
SQL> -- for the second query:
SQL> --
SQL> SELECT * FROM EMPLOYEES E WHERE E.STATUS_CODE = "2";
0 rows selected
SQL> --
SQL> SELECT * FROM EMPLOYEES E WHERE E.STATUS_CODE = "3";
EMPLOYEE_ID LAST_NAME FIRST_NAME MIDDLE_INITIAL
ADDRESS_DATA_1 ADDRESS_DATA_2 CITY
STATE POSTAL_CODE SEX BIRTHDAY STATUS_CODE
00165 Smith Terry D
120 Tenby Dr. Chocorua
NH 03817 M 15-May-1954 3
00178 Goldstone Neal NULL
194 Lyons Av, Colebrook
NH 03576 M 25-Apr-1952 3
.
.
.
00358 Lapointe Jo Ann C
70 Tenby Dr. Chocorua
NH 03817 F 24-Feb-1931 3
12 rows selected
SQL> --
SQL> ROLLBACK;
Example 3: Defining a trigger that updates a sales summary
The following example defines a trigger that updates a monthly
sales total after each daily sale is made.
SQL> --
SQL> -- Create the table to keep track of monthly sales:
SQL> CREATE TABLE MONTHLY_SALES
cont> ( SALES_AMOUNT INTEGER);
SQL> --
SQL> -- Create the table to keep track of sales made today:
SQL> CREATE TABLE DAILY_SALES
cont> ( SALES_AMOUNT INTEGER);
SQL> --
SQL> -- Assume that $250.00 of sales have been made during the current month:
SQL> INSERT INTO MONTHLY_SALES
cont> (SALES_AMOUNT) VALUES (250);
1 row inserted
SQL> --
SQL> -- After adding a new value to the SALES_AMOUNT column in
SQL> -- DAILY_SALES table, SQL updates the SALES column in
SQL> -- the MONTHLY_SALES table with the amount of the new sale:
SQL> CREATE TRIGGER UPDATE_SALES_TOTAL_ON_NEW_SALE
cont> AFTER INSERT ON DAILY_SALES
cont> (UPDATE MONTHLY_SALES M
cont> SET M.SALES_AMOUNT = M.SALES_AMOUNT + DAILY_SALES.SALES_AMOUNT)
cont> FOR EACH ROW;
SQL> --
SQL> -- The following statement records a new $5.00 sale for today:
SQL> INSERT INTO DAILY_SALES
cont> (SALES_AMOUNT) VALUES (5);
1 row inserted
SQL> --
SQL> -- The value for the SALES_AMOUNT column of the DAILY_SALES table
SQL> -- is $5.00 and the value of the SALES_AMOUNT column of the
SQL> -- MONTHLY_SALES table is $255.00:
SQL> SELECT * FROM DAILY_SALES;
SALES_AMOUNT
5
1 row selected
SQL> --
SQL> SELECT * FROM MONTHLY_SALES;
SALES_AMOUNT
255
1 row selected
SQL> --
SQL> -- When a new $9.00 sale is made, the values in the two rows of the
SQL> -- SALES_AMOUNT column of the DAILY_SALES table are $5.00 and $9.00
SQL> -- and the value of the SALES_AMOUNT column of the MONTHLY_SALES
SQL> -- table is $264.00:
SQL> INSERT INTO DAILY_SALES
cont> (SALES_AMOUNT) VALUES (9);
1 row inserted
SQL> --
SQL> SELECT * FROM DAILY_SALES;
SALES_AMOUNT
5
9
2 rows selected
SQL> --
SQL> SELECT * FROM MONTHLY_SALES;
SALES_AMOUNT
264
1 row selected
SQL> --
SQL> ROLLBACK;
SQL> --
Example 4: Defining a trigger that sets column values to null
Before the STATUS_CODE column in the WORK_STATUS table is
deleted, this trigger causes the associated WORK_STATUS columns
in the EMPLOYEES table to be set to null.
SQL> CREATE TRIGGER STATUS_CODE_ON_DELETE_SET_NULL
cont> BEFORE DELETE ON WORK_STATUS
cont> (UPDATE EMPLOYEES E SET E.STATUS_CODE = NULL
cont> WHERE E.STATUS_CODE = WORK_STATUS.STATUS_CODE)
cont> FOR EACH ROW;
SQL> --
SQL> -- Delete any row in the WORK_STATUS table where the STATUS_CODE
SQL> -- column has a value of 1:
SQL> DELETE FROM WORK_STATUS WS WHERE WS.STATUS_CODE = "1";
1 row deleted
SQL> --
SQL> -- This trigger sets the STATUS_CODE column value to null in many
SQL> -- rows in the EMPLOYEES table:
SQL> SELECT * FROM EMPLOYEES E WHERE E.STATUS_CODE IS NULL;
EMPLOYEE_ID LAST_NAME FIRST_NAME MIDDLE_INITIAL
ADDRESS_DATA_1 ADDRESS_DATA_2 CITY
STATE POSTAL_CODE SEX BIRTHDAY STATUS_CODE
00416 Ames Louie A
61 Broad st. NULL Alton
NH 03809 M 13-Apr-1941 NULL
00374 Andriola Leslie Q
111 Boston Post Rd. NULL Salisbury
NH 03268 M 19-Mar-1955 NULL
.
.
.
00200 Ziemke Al F
121 Putnam Hill Rd. NULL Winnisquam
NH 03289 M 27-Oct-1928 NULL
88 rows selected
SQL> ROLLBACK;
Example 5: Defining a trigger that prevents deletion of a row
that exists in two tables
Suppose that a user wants to delete only those rows in the JOB_
HISTORY table that do not also exist in the JOBS table. This is
difficult to do with constraints because a row can exist in one
table with a key number that does not exist in the other table.
The following statement creates a trigger that causes an error
when the user tries to delete a row that exists in table JOB_
HISTORY.
SQL> CREATE TRIGGER DELETE_GUARD
cont> BEFORE DELETE ON JOB_HISTORY
cont> WHEN EXISTS (SELECT JOBS.JOB_CODE FROM JOBS
cont> WHERE JOBS.JOB_CODE=JOB_HISTORY.JOB_CODE)
cont> (ERROR) FOR EACH ROW;
SQL> --
SQL> -- Now attempt a deletion that violates the trigger.
SQL> --
SQL> DELETE FROM JOB_HISTORY WHERE JOB_CODE = 'DMGR';
%RDB-E-TRIG_INV_UPD, invalid update; encountered error condition
defined for trigger
-RDMS-E-TRIG_ERROR, trigger DELETE_GUARD forced an error
-RDB-F-ON_DB, on database DISK1:[DEPT3.SQL]MF_PERSONNEL.RDB;1
Example 6: Defining a trigger that saves audit information
SQL> -- Create new table to record changes made to
SQL> -- EMPLOYEES table
SQL> CREATE TABLE AUDIT_TRAIL
cont> (LOG DATE VMS,
cont> PERSON CHAR(31),
cont> TBL_NAME CHAR(10),
cont> OPER CHAR(1));
SQL> COMMIT;
SQL> -- Create a trigger so that each time
SQL> -- an INSERT operation is performed,
SQL> -- a record is stored in the AUDIT_TRAIL table.
SQL> CREATE TRIGGER EMPS_TRIGGER
cont> AFTER INSERT
cont> ON EMPLOYEES
cont> (INSERT INTO AUDIT_TRAIL
cont> VALUES (CURRENT_TIMESTAMP,
cont> CURRENT_USER, 'EMPLOYEES', 'I'))
cont> FOR EACH STATEMENT;
SQL> -- The AUDIT_TRAIL table currently has no records.
SQL> SELECT * FROM AUDIT_TRAIL;
0 rows selected
SQL> -- Insert a record into EMPLOYEES
SQL> INSERT INTO EMPLOYEES
cont> (EMPLOYEE_ID, LAST_NAME)
cont> VALUES ('00964', 'FRENCH');
1 row inserted
SQL> -- See if trigger updated the AUDIT_TRAIL table.
SQL> SELECT * FROM AUDIT_TRAIL;
LOG PERSON TBL_NAME OPER
17-JUN-2003 15:04:31.43 STEWART EMPLOYEES I
1 row selected
Example 7: Using TRACE as a trigger action
SQL> set flags 'TRACE';
SQL> create table M_TABLE (a integer, b integer);
SQL>
SQL> create trigger T_A
cont> after insert on M_TABLE
cont> (trace 'in a trigger: ' || cast(M_TABLE.a as varchar(10)))
cont> for each row;
SQL>
SQL> insert into M_TABLE (a, b) values (1, 10);
~Xt: in a trigger: 1
1 row inserted
SQL>
Example 8: Using SIGNAL as a trigger action
SQL> create table M_TABLE (a integer, b integer);
SQL>
SQL> create trigger T_A
cont> after insert on M_TABLE2
cont> when (M_TABLE2.a is not null)
cont> (signal '12345' ('in a trigger: '
cont> || cast(M_TABLE2.a as varchar(10))))
cont> for each row;
SQL>
SQL> insert into M_TABLE2 (a, b) values (1, 10);
%RDB-E-SIGNAL_SQLSTATE, routine "T_A" signaled SQLSTATE "12345"
-RDB-I-TEXT, in a trigger: 1
SQL>
Example 9: Using CALL as a trigger action
SQL> create module M_MODULE
cont> language SQL
cont>
cont> procedure M_K (in :a int);
cont> trace 'called from a trigger: ' || cast(:a as varchar(10));
cont>
cont> end module;
SQL>
SQL> create table M_TABLE (a integer, b integer);
SQL>
SQL> create trigger T_A
cont> after insert on M_TABLE
cont> (call M_K (M_TABLE.a))
cont> for each row;
SQL>
SQL> insert into M_TABLE (a, b) values (1, 10);
~Xt: called from a trigger: 1
1 row inserted
SQL>