set serveroutput on;

Statement level trigger

create table audit_test (
id number,
name varchar2(10),
address varchar(15)
)

create table audit_test2(
dml_oprn varchar2(30),
user_name varchar2(30),
usr_ts date );

CREATE OR REPLACE TRIGGER audittrig 
after insert or update or delete 
on audit_test
declare msg varchar2(50);
begin
if inserting thenmsg:= 'Insertion done';
elsif updating thenmsg:= 'updation done';
elsif deleting thenmsg:= 'deletion done';
end if;
insert into audit_test2 values(msg,user,sysdate);
end;

Perform query

select * from audit_test2;
insert into audit_test values (4,'mahesh','juhu');
update audit_test set name = 'sachin' where id = 2;
delete from audit_test where id =3;
select * from audit_test2;

ROW LEVEL TRIGGER

create table cust2 ( sal_diff number) ;

CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE 
ON customers 
FOR EACH ROW  
DECLARE    sal_diff number; 
BEGIN    sal_diff := :NEW.salary  - :OLD.salary;       
insert into cust2 values (sal_diff);    
dbms_output.put_line('Old salary: ' || :OLD.salary);    
dbms_output.put_line('New salary: ' || :NEW.salary);    
dbms_output.put_line('Salary difference: ' || sal_diff);   
END; /

Example :2
 
CREATE TABLE new_employee(
employee_id    NUMBER,
employee_name VARCHAR2(1000),
creation_date DATE,
created_by    VARCHAR2(1000));

CREATE TABLE new_employee_duplicate AS (SELECT * FROM new_employee);        

--TRIGGER--

CREATE OR REPLACE TRIGGER new_employee_trigger
BEFORE DELETE
ON new_employee
FOR EACH ROW
BEGIN  INSERT INTO new_employee_duplicate  
VALUES (:old.employee_id,:old.employee_name,:old.creation_date,:old.created_by);
END;

INSERT INTO new_employeeVALUES(2, ‘Emp xyz’,SYSDATE,’SCOTT’);
select * from new_employee;select * from new_employee_duplicate;
delete from new_employee where employee_id=2;
select * from new_employee_duplicate;

 

——–DCL TRIGGER——

CREATE TABLE AUDIT_DDL (  d date,  OSUSER varchar2(255),  CURRENT_USER varchar2(255),  HOST varchar2(255),  TERMINAL varchar2(255),  owner varchar2(30),  type varchar2(30),  name varchar2(30),  sysevent varchar2(30));    set serveroutout on        create or replace trigger audit_ddl_trg after ddl on schema begin  if (ora_sysevent=’TRUNCATE’)  then    null; — I do not care about truncate  else    insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)    values(      sysdate,      sys_context(‘USERENV’,’OS_USER’) ,      sys_context(‘USERENV’,’CURRENT_USER’) ,      sys_context(‘USERENV’,’HOST’) ,       sys_context(‘USERENV’,’TERMINAL’) ,      ora_dict_obj_owner,      ora_dict_obj_type,      ora_dict_obj_name,      ora_sysevent    );  end if;end;
select * from  AUDIT_DDL;

 

——-INSTEAD OF ————-

create table Employees(
ID                 VARCHAR2(4 BYTE)         NOT NULL,     First_Name         VARCHAR2(10 BYTE),     Last_Name          VARCHAR2(10 BYTE),     Start_Date         DATE,     End_Date           DATE,     Salary             Number(8,2),     City               VARCHAR2(10 BYTE),     Description        VARCHAR2(15 BYTE)   )
insert into Employeesvalues (’01’,’Jason’,    ‘Martin’,  to_date(‘19960725′,’YYYYMMDD’), to_date(‘20060725′,’YYYYMMDD’), 1234.56, ‘Toronto’,  ‘Programmer’)    insert into Employeesvalues(’02’,’Alison’,   ‘Mathews’, to_date(‘19760321′,’YYYYMMDD’), to_date(‘19860221′,’YYYYMMDD’), 6661.78, ‘Vancouver’,’Tester’);
insert into Employeesvalues(’03’,’James’,    ‘Smith’,   to_date(‘19781212′,’YYYYMMDD’), to_date(‘19900315′,’YYYYMMDD’), 6544.78, ‘Vancouver’,’Tester’);
insert into Employeesvalues(’04’,’Celia’,    ‘Rice’,    to_date(‘19821024′,’YYYYMMDD’), to_date(‘19990421′,’YYYYMMDD’), 2344.78, ‘Vancouver’,’Manager’);
insert into Employeesvalues(’05’,’Robert’,   ‘Black’,   to_date(‘19840115′,’YYYYMMDD’), to_date(‘19980808′,’YYYYMMDD’), 2334.78, ‘Vancouver’,’Tester’);
insert into Employeesvalues(’06’,’Linda’,    ‘Green’,   to_date(‘19870730′,’YYYYMMDD’), to_date(‘19960104′,’YYYYMMDD’), 4322.78,’New York’,  ‘Tester’);
insert into Employeesvalues(’07’,’David’,    ‘Larry’,   to_date(‘19901231′,’YYYYMMDD’), to_date(‘19980212′,’YYYYMMDD’), 7897.78,’New York’,  ‘Manager’);
insert into Employeesvalues(’08’,’James’,    ‘Cat’,     to_date(‘19960917′,’YYYYMMDD’), to_date(‘20020415′,’YYYYMMDD’), 1232.78,’Vancouver’, ‘Tester’);

 

SQL> select * from Employees

 

ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION—- ——————– ——————– ——— ——— ———- ———- —————01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer02   Alison               Mathews              21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester03   James                Smith                12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester04   Celia                Rice                 24-OCT-82 21-APR-99    2344.78 Vancouver  Manager05   Robert               Black                15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester06   Linda                Green                30-JUL-87 04-JAN-96    4322.78 New York   Tester07   David                Larry                31-DEC-90 12-FEB-98    7897.78 New York   Manager08   James                Cat                  17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.

create or replace view v_employees asselect id, first_Namefrom employeeorder by first_Name;

create or replace trigger v_emp_iuINSTEAD OF UPDATEon v_employeesdeclarev_error VARCHAR2(256);beginif updating(‘ID’)thenv_error:=’You cannot update the PK!’;raise_application_error (-20999,v_error);elseupdate employeesset first_Name = :new.first_Namewhere id = :old.id;end if;end;
update v_employee set first_name=’aa’;
update v_employee set id = 0;