Quick Search:
 
 Oracle PL/SQL: Create trigger in Oracle Jump to:  
Category: >> Oracle PL/SQL >> Create trigger in Oracle  

<< lastnext >>

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;
/
 


 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org