CseWay

A Way For Learning

Triggers : PL-SQL

No comments
Triggers are used to add restrictions to data entered into the tables.It is used to store the details if there are any changes made in fields of a table.By using triggers we can know where the change has been happened ,what was the value that was changed and who changed it.

Triggers are used on the DML statements insert,update and delete. Triggers can be applied either before or after the change.

6 Types of Triggers:
  1. AFTER INSERT
  2. BEFORE INSERT
  3. AFTER DELETE
  4. BEFORE DELETE
  5. AFTER UPDATE
  6. BEFORE UPDATE
Example :
The following program

CREATE OR REPLACE TRIGGER T1
BEFORE INSERT ON TICKET
FOR EACH ROW
BEGIN
IF(:NEW.JOURNEY_DATE<SYSDATE) THEN
RAISE_APPLICATION_ERROR(-20001,'Invalid Date');
END IF;
END;

 Log or Audit tables for triggers:
These tables are used to store the information about the changes they are taken place in the table.One can store the user name,the old and new values in the table while executing the triggers.
Example
 Log table
CREATE TABLE LOG(      USERNAME VARCHAR2(20),OLDVALUE VARCHAR 2(20),NEWVALUE VARCHAR2(20)   );


Log table in trigger
CREATE OR REPLACE TRIGGER TRIG_NAME
before update or delete on TABLE_NAME for each row                                                DECLARE                                                                                                                                 T_USERNAME VARCHAR2(20)
begin
if deleting or updating then                                                                                                   SELECT USER INTO T_USERNAME FROM DUAL;
  insert into comment_audit(
    USERNAME, OLDVALUE,NEWVAL
  )
  values (                                                                                                                                     T_USERNAME,
    :old.COLUMN_NAME,
    :NEW.COLUMN_NAME
  );
end if;
end;

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )