Snippet Name: Create trigger in Oracle
Description: The code associated with a trigger is fired when a specified event occurs. The events can either be a DML event, a DDL event or a database event. (DDL event and database event triggers are also called system triggers)
Also see: » TRIGGER Example
» Trigger sample
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: February 27th, 2009
|
CREATE TRIGGER trigger-name before event
CREATE TRIGGER trigger-name after event
CREATE TRIGGER trigger-name instead OF event
/*
Event
The code associated with a trigger is fired when a specified
event occurs. The events can either be a DML event, a DDL event
or a database event. (DDL event and database event triggers are
also called system triggers)
*/
DML event
DELETE ON table-name
INSERT ON table-name
UPDATE ON table-name
UPDATE OF column-name ON table-name
UPDATE OF column-name-1, column-name-2 ON ...
DELETE OR INSERT ON table-name
DELETE OR UPDATE ON table-name
....
DDL event
ddl-event ON schema
ddl-event OR ddl-event ON schema
ddl-event OR ddl-event OR ddl-event ... ON schema
ddl-event ON database schema
ddl-event OR ddl-event ON database
ddl-event OR ddl-event OR ddl-event ... ON database
Database event
database-event ON schema
database-event OR database-event ON schema
database-event OR database-event OR database-event ON schema ...
database-event ON database
database-event OR database-event ON database
database-event OR database-event OR database-event ON database ...
/*
The following database events can be caught:
* Servererror
* logon
* Logoff
* startup
* shutdown
* suspend
Prerequisites
dbmsstdx.sql must have been called. Normally, catalog.sql is
run after the creation of a database. Catalog.sql in turn calls
dbmsstdx.sql.
*/
before INSERT OR UPDATE
CREATE OR REPLACE TRIGGER <TRIGGER_NAME>
before INSERT OR UPDATE
ON <table_name>
FOR each ROW
DECLARE
<VARIABLE DECLARATIONS>
BEGIN
<CODE>
EXCEPTION
<EXCEPTION HANDLERS>
END <TRIGGER_NAME>;
/
/* After Logon on database */
CREATE TABLE logon_tbl (who VARCHAR2(30), WHEN DATE);
CREATE OR REPLACE TRIGGER trg_logon_db
after logon ON database
BEGIN
INSERT INTO logon_tbl (who, WHEN) VALUES (USER, SYSDATE);
END;
/
|