You create a trigger using the CREATE TRIGGER statement.
The simplified syntax for the CREATE TRIGGER statement is as follows:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} trigger_event
ON table_name
[FOR EACH ROW [WHEN trigger_condition]]
BEGIN
trigger_body
END trigger_name;
where
- OR REPLACE specifies the trigger is to replace an existing trigger if present.
- BEFORE specifies the trigger fires before the triggering event is performed.
- AFTER specifies the trigger fires after the triggering event is performed.
- INSTEAD OF specifies the trigger fires instead of performing the triggering event.
- trigger_event specifies the event that causes the trigger to fire.
- table_name specifies the table that the trigger references.
- FOR EACH ROW specifies the trigger is a row-level trigger.
- A row-level trigger is run for each row when the trigger fires.
- If you omit FOR EACH ROW, the trigger is a statement-level trigger.
- A statement-level trigger is run once when the trigger fires regardless of the number of rows affected.
- trigger_condition specifies a Boolean condition that limits when a trigger actually runs its code.
- trigger_body contains the SQL and PL/SQL statements that perform the trigger's task.
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
SQL>
SQL> CREATE TABLE employee_history (
2 name VARCHAR2(100),
3 description VARCHAR2(255),
4 occurred_on DATE);
Table created.
SQL>
SQL> CREATE TABLE employee_compensation (
2 company VARCHAR2(100),
3 name VARCHAR2(100),
4 compensation NUMBER,
5 layoffs NUMBER);
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE employee_audit (
2 name IN VARCHAR2,
3 description IN VARCHAR2,
4 occurred_on IN DATE
5 )
6 IS
7 PRAGMA AUTONOMOUS_TRANSACTION;
8 BEGIN
9 INSERT INTO employee_history VALUES (
10 employee_audit.name,
11 employee_audit.description,
12 employee_audit.occurred_on
13 );
14 COMMIT;
15 END;
16 /
Procedure created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER aft_ins_ceo_comp
2 AFTER INSERT ON employee_compensation FOR EACH ROW
3 DECLARE
4 ok BOOLEAN := FALSE;
5 BEGIN
6 IF ok
7 THEN
8 employee_audit (
9 :new.name, 'AFTER INSERT', SYSDATE);
10 ELSE
11 RAISE VALUE_ERROR;
12 END IF;
13 END;
14 /
Trigger created.
SQL>
SQL>
SQL> SELECT name,
2 description,
3 TO_CHAR (occurred_on, 'MM/DD/YYYY HH:MI:SS') occurred_on
4 FROM employee_history;
no rows selected
SQL>
SQL> BEGIN
2 INSERT INTO employee_compensation VALUES ('M', 'J', 9100000, 2700);
3
4 INSERT INTO employee_compensation VALUES ('A', 'H', 33200000, 3300);
5
6 INSERT INTO employee_compensation VALUES ('E', 'G', 10700000, 20100);
7
8 END;
9 /
BEGIN
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "JAVA2S.AFT_INS_CEO_COMP", line 9
ORA-04088: error during execution of trigger 'JAVA2S.AFT_INS_CEO_COMP'
ORA-06512: at line 2
SQL>
SQL>
SQL> SELECT name,
2 description,
3 TO_CHAR (occurred_on, 'MM/DD/YYYY HH:MI:SS') occurred_on
4 FROM employee_history;
no rows selected
SQL>
SQL>
SQL>
SQL> DROP TABLE employee_compensation;
Table dropped.
SQL>
SQL> DROP TABLE employee_history;
Table dropped.
SQL>