Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: Create trigger in Oracle Jump to:  
Category: >> Oracle PL/SQL >> Create trigger in Oracle Bookmark and Share

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


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 98 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?