database triggers

Topics: Database trigger, SQL, PostgreSQL Pages: 19 (1673 words) Published: November 11, 2013


Oracle Table Triggers
Version 11.1
 
General
Data Dictionary Views Related
To DDL Triggers
trigger$
dba_triggers
all_triggers
user_triggers

System Privileges Related To Table Triggers
create trigger
create any trigger
administer database trigger
alter any trigger
drop any trigger

Table Trigger Firing Options
-- before constraints are applied
BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE

-- after constraints are applied
AFTER INSERT
AFTER UPDATE
AFTER DELETE
Transaction Model
Oracle transactions are atomic. No commit or rollback are allowed in a trigger. Maximum trigger size
32K - but you can call procedures and function in triggers to perform processing  
Create Statement Level Triggers (the default)

Statement Level Trigger With A Single Action
CREATE OR REPLACE TRIGGER
[]
[OR OR ]
ON
[FOLLOWS ]

DECLARE
 
BEGIN
 
EXCEPTION
 
END ;
/

CREATE TABLE orders (
somecolumn VARCHAR2(20),
numbercol  NUMBER(10),
datecol    DATE);

CREATE OR REPLACE TRIGGER statement_level
BEFORE UPDATE
ON orders

DECLARE
 vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
  dbms_output.put_line(vMsg);
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders SET somecolumn = 'XYZ';

Statement Level Trigger With  Multiple Actions

CREATE OR REPLACE TRIGGER statement_level
AFTER INSERT OR UPDATE OR DELETE
ON orders

DECLARE
 vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
  IF INSERTING THEN
    dbms_output.put_line(vMsg || ' When Inserting');
  ELSIF UPDATING THEN
    dbms_output.put_line(vMsg || ' When Updating');
  ELSIF DELETING THEN
    dbms_output.put_line(vMsg || ' When Deleting');
  END IF;
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders SET somecolumn = 'DEF' WHERE ROWNUM = 1;

DELETE FROM orders WHERE ROWNUM = 1;
 
Create Row Level Triggers
Note: AFTER row triggers create less UNDO than BEFORE row triggers so use AFTER when possible.

Row Level Trigger ... most common usage to provide a surrogate key from a sequence CREATE OR REPLACE TRIGGER
[FOLLOWS ]
[]
OR OR
[OF ]
ON
REFERENCING NEW AS OLD AS PARENT AS
FOR EACH ROW

DECLARE
 
BEGIN
 
EXCEPTION
 
END ;
/

CREATE TABLE t (
rid NUMBER(5),
col VARCHAR2(3));

ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (rid)
USING INDEX;

CREATE SEQUENCE seq_t;

CREATE OR REPLACE TRIGGER row_level
BEFORE INSERT
ON t
FOR EACH ROW

BEGIN
  SELECT seq_t.NEXTVAL
  INTO :NEW.rid
  FROM dual;
  dbms_output.put_line(:NEW.rid);
END row_level;
/

INSERT INTO t (col) VALUES ('A');
INSERT INTO t (col) VALUES ('B');
INSERT INTO t (col) VALUES ('C');

SELECT * FROM t;

Row Level Trigger With A Single Action

CREATE OR REPLACE TRIGGER row_level
BEFORE UPDATE
ON orders
FOR EACH ROW

DECLARE
 vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
  dbms_output.put_line(vMsg);
END row_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');
INSERT INTO orders (somecolumn) VALUES ('ABC');
INSERT INTO orders (somecolumn) VALUES ('ABC');

SELECT * FROM orders;

UPDATE orders SET somecolumn = 'XYZ';

Row Level Trigger With   Multiple Actions

CREATE OR REPLACE TRIGGER statement_level
AFTER INSERT OR UPDATE OR DELETE
ON orders
FOR EACH ROW

DECLARE
 vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
BEGIN
  IF INSERTING THEN
    dbms_output.put_line(vMsg || ' On Insert');
  ELSIF UPDATING THEN
    dbms_output.put_line(vMsg || ' On Update');
  ELSIF DELETING THEN
    dbms_output.put_line(vMsg || ' On Delete');
  END IF;
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders
SET somecolumn = 'ZZT';

DELETE FROM orders WHERE rownum < 4;

Row Level Trigger With OF Clause

CREATE OR REPLACE...
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Essay about Saving File in Oracle Database
  • Triggers Essay
  • Database Design: Notes Essay
  • database Essay
  • Oracle Database: SQL Fundamentals Essay
  • database connectivity in vb Essay
  • Trigger Finger Essay
  • Database Essay

Become a StudyMode Member

Sign Up - It's Free