Tag Archives: enable or disable trigger

Triggers in Oracle

Triggers in Oracle

Triggers are special kind of procedure which is fired automatically don’t need to call by user explicitly. It automatically fired on some event happen insert update delete operation on which it built or code.

Syntax:
CREATE [OR REPLACE] TRIGGER schema.trigger-name
{BEFORE | AFTER} dmlevent ON tablename
[FOR EACH ROW]
[DECLARE ...]
BEGIN
-- PL/SQL code goes here.
[EXCEPTION ...]
END;
/
BEFORE: defines the trigger is executed before the statement executed on table.
AFTER: defines the trigger is executed after the statement executed on table.
ROW LEVEL TRIGGER:FOR EACH ROW Clause is used for defined, trigger is executed at each row modification.
STATEMENT LEVEL TRIGGER: without clause FOR EACH ROW, executed at start and end at table level.
Example of ROW-LEVEL or STATEMENT-LEVEL trigger:
Before statement-level trigger executes
Before row-level trigger executes
One row is updated
After row-level trigger executes
...
Before row-level trigger executes
Millionth row is updated
After row-level trigger executes
After statement-level trigger executes
Note: Basically Statement trigger is used data is in bulk inserted or updated or deleted operation is started.

Check trigger information in database

SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE TABLE_NAME = 'SALES'
SELECT * FROM DBA_TRIGGERS WHERE TRIGGER_NAME = 'AW_DROP_TRG';
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER';
SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'TRIGGER_NAME';

Get source or code for trigger

set heading off;
set echo off;
Set pages 999;
set long 20000;
select dbms_metadata.get_dependent_ddl('TRIGGER','EMP','SCOTT') output from dual;

Enable or Disable the Triggers

--Enable or disable single trigger
ALTER TRIGGER triggername DISABLE;
ALTER TRIGGER triggername ENABLE;

--Enable or disable all trigger at table level
ALTER TABLE tablename DISABLE ALL TRIGGERS;
ALTER TABLE tablename ENABLE ALL TRIGGERS;

--Enable or disable all trigger at schema level
select 'ALTER TABLE '||table_name||' DISABLE ALL TRIGGERS;' from user_triggers;
select 'ALTER TABLE '||table_name||' ENABLE ALL TRIGGERS;' from user_triggers;

Errors:
ORA-04082: NEW or OLD references not allowed in table level triggers
Solutions:
1. Eliminate the use of NEW OR OLD your statement in trigger.
2. Another option make add FOR EACH ROW clause in trigger.

Example of ROW LEVEL Trigger with OLD OR NEW keyword
We have table hr.sales for keep current value of sales data and hr.sales_bkp for taking backup or modification going in sales for keep track. We created trigger on sales table for taking backup.
OLD : old is pointing to UPDATE or DELETE old records.
NEW : New is pointing to INSERT or UPDATE new records.
Note: Update statement is having both OLD or NEW data pointer.

CREATE OR REPLACE TRIGGER t_test
BEFORE INSERT OR UPDATE OR DELETE ON hr.sales
FOR EACH ROW
BEGIN
IF inserting THEN
insert into hr.sales_bkp values ( :new.id, :new.name, :new.sales, sysdate ,'I');
END IF;
-- always set the modified date to now
IF updating THEN
insert into hr.sales_bkp values ( :old.id, :old.name, :old.sales, sysdate ,'U');
END IF;
IF DELETING THEN
insert into hr.sales_bkp values( :old.id, :old.name, :old.sales, sysdate ,'D');
END IF;
END;
/
Trigger created.

SQL> insert into sales values (1,'LG WM',25000);
1 row created.

SQL> update sales set name='SONY WM' where id = '1';
1 row updated.

SQL> update sales set sales='35000' where id = '1';
1 row updated.

SQL> delete from sales;
1 row deleted.

SQL> select * from sales_bkp;

  ID NAME            SALES CREATED_D F
---- ---------- ---------- --------- -
   1 LG WM           25000 11-SEP-18 I
   1 LG WM           25000 11-SEP-18 U
   1 SONY WM         25000 11-SEP-18 U
   1 SONY WM         35000 11-SEP-18 D

Example of STATMENT LEVEL TRIGGERS

--Create Statment Level Trigger
CREATE OR REPLACE TRIGGER trigger_Statement_test
BEFORE INSERT OR UPDATE OR DELETE ON hr.sales
BEGIN
IF inserting THEN
insert into hr.sales_bkp values ( '10','INSERTED',0,sysdate,'I');
END IF;
-- always set the modified date to now
IF updating THEN
insert into hr.sales_bkp values ( '10','UPDATED',0,sysdate,'U');
END IF;
IF DELETING THEN
insert into hr.sales_bkp values ( '10','DELETED',0,sysdate,'D');
END IF;
END;
/
--In this bulk operation of insert statement, Only one entry due to statement trigger.
insert into sales select * from sales_history;
4 rows inserted

--Check SALES_BKP table having STATEMENT LEVEL trigger entry and also have entries for ROW LEVEL trigger created in above example.
select * from sales_bkp;

   ID NAME            SALES CREATED_D F
----- ---------- ---------- --------- -
   10 INSERTED            0 11-SEP-18 I
    1 LG              20000 11-SEP-18 I
    2 SONY            20000 11-SEP-18 I
    3 DELL            25000 11-SEP-18 I
    4 LENOVO          40000 11-SEP-18 I

--Now Sales table has two triggers
SQL> select trigger_name from all_triggers where table_name = 'SALES';
TRIGGER_NAME
-----------------------
T_TEST
TRIGGER_STATEMENT_TEST

Advertisements

Check Enable and Disable Triggers in Oracle

Enabling and Disabling Triggers in Oracle

In simple language trigger is a task which executed and linked with other task.
If one task linked with other task then on execution of first task then link task(i.e trigger) is also executed.

Database triggers are act like procedures that are stored in the database and fired on specific condition. Database trigger executed with DML, DDL and database event like shutdown startup etc.
Example you create trigger on insert of employee table whenever the insert operation performed on employee table associated trigger is fired and run its procedure.

Note: By default on creating trigger it is enabled.

Create syntax of trigger

CREATE TRIGGER scott.emp_history_changes
AFTER
DELETE OR INSERT OR UPDATE
ON scott.emp
.
.
.
pl/sql block

Enable the Triggers

-- Enable trigger by name
ALTER TRIGGER trigger_name ENABLE;

— IF table having more than one trigger
ALTER TABLE table_name ENABLE ALL TRIGGERS;

Disable the trigger

-- disable trigger by name
ALTER TRIGGER trigger_name DISABLE;

— Disable all trigger for the table
ALTER TABLE table_name DISABLE ALL TRIGGERS;

Check trigger information

select owner,trigger_name,table_name,status from dba_triggers;