This thread contains some useful tips/sample codes regarding TRIGGERS in oracle, that the forum members may find useful.
TRIGGERS:
===============
Database trigger is a PL/SQL block that is executed on an event in the database.
The event is related to a particular data manipulation of a table such as inserting, deleting or updating a row of a table.
Triggers may be used :
1.To implement complex business rule, which cannot be implemented using integrity constraints.
2.To audit the process. For example, to keep track of changes made to a table.
3. To automatically perform an action when another concerned action takes place. For example, updating a table whenever there is an insertion of a row into another table.
Types of Triggers:
------------------------
Depending upon when a trigger is fired it may be classified as :
a.Statement-level trigger
b.Row-level trigger
c.Before triggers
d.After triggers
e.Instead of triggers
Syntax:
-----------
CREATE [OR REPLACE] TRIGGER triggername
{BEFORE | AFTER | INSTEAD OF}
{DELETE | INSERT | UPDATE [OF columns]}
[OR {DELETE | INSERT |UPDATE [OF columns]}]...
ON table_name|SCHE MA
[REFERENCING [OLD AS old] [NEW AS new]]
[FOR EACH ROW [WHEN condition]]
<PL/SQL block>
Trigger Sample Ex #1
=============== =====
[code=oracle]
CREATE OR REPLACE TRIGGER MYTRIG AFTER INSERT OR DELETE ON EMP
DECLARE
MCOUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO MCOUNT FROM EMP;
--checks the number of records in the table.
DBMS_OUTPUT.PUT _LINE('THE TOTAL NUMBER OF EMPLOYEE ARE:' || MCOUNT);
--and displays the same after the event .
END;
[/code]
Trigger Sample Ex #2
=============== =
[CODE=oracle]CREATE OR REPLACE TRIGGER ONLYPOSITIVE BEFORE INSERT OR UPDATE OF SAL ON EMP FOR EACH ROW
BEGIN
IF :NEW.SAL<0 THEN
--checks of the value inserted into teh sal column is less than 0.
RAISE_APPLICATI ON_ERROR(-20100,'PLEASE INSERT A POSITIVE VALUE');
--if valus is less than 0 ,then raise the exception.
END IF;
END;[/CODE]
Note:-Trigger does not allow negative values to insert/update to the table.
trigger example #3
=============== ===
[CODE=oracle]CREATE OR REPLACE TRIGGER NODELETE BEFORE INSERT OR UPDATE OR DELETE ON DEPT FOR EACH ROW
BEGIN
IF INSERTING THEN
RAISE_APPLICATI ON_ERROR(-20020,'CAN''T INSERT NEW RECORDS TO THIS TABLE');
ELSIF UPDATING THEN
RAISE_APPLICATI ON_ERROR(-20021,'CAN''T UPDATE RECORDS OF THIS TABLE');
ELSIF DELETING THEN
RAISE_APPLICATI ON_ERROR(-20022,'YOU ARE NOT AUTHORISED TO DELETE THESE RECORDS');
END IF;
END;[/CODE]
Note:- the triggermakes the trigger readonly by not allowing any DML on it.
Trigger Sample Ex #4
=============== =
[CODE=oracle]
create or replace trigger mytrig
before insert on emp
declare
a char(3);
begin
--selects the system day to the vaiable.
select to_char(sysdate ,'dy') into a from dual;
if a in('sun','sat') then
--restrict any transaction on saturday and sunday.
Raise_applicati on_error(-20004,'Cannot do manipulation today');
end if;
end;[/CODE]
Note:--This trigger restricts transactions on the table on weekends.
Also check TRIGGERS - 2
TRIGGERS:
===============
Database trigger is a PL/SQL block that is executed on an event in the database.
The event is related to a particular data manipulation of a table such as inserting, deleting or updating a row of a table.
Triggers may be used :
1.To implement complex business rule, which cannot be implemented using integrity constraints.
2.To audit the process. For example, to keep track of changes made to a table.
3. To automatically perform an action when another concerned action takes place. For example, updating a table whenever there is an insertion of a row into another table.
Types of Triggers:
------------------------
Depending upon when a trigger is fired it may be classified as :
a.Statement-level trigger
b.Row-level trigger
c.Before triggers
d.After triggers
e.Instead of triggers
Syntax:
-----------
CREATE [OR REPLACE] TRIGGER triggername
{BEFORE | AFTER | INSTEAD OF}
{DELETE | INSERT | UPDATE [OF columns]}
[OR {DELETE | INSERT |UPDATE [OF columns]}]...
ON table_name|SCHE MA
[REFERENCING [OLD AS old] [NEW AS new]]
[FOR EACH ROW [WHEN condition]]
<PL/SQL block>
Trigger Sample Ex #1
=============== =====
[code=oracle]
CREATE OR REPLACE TRIGGER MYTRIG AFTER INSERT OR DELETE ON EMP
DECLARE
MCOUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO MCOUNT FROM EMP;
--checks the number of records in the table.
DBMS_OUTPUT.PUT _LINE('THE TOTAL NUMBER OF EMPLOYEE ARE:' || MCOUNT);
--and displays the same after the event .
END;
[/code]
Trigger Sample Ex #2
=============== =
[CODE=oracle]CREATE OR REPLACE TRIGGER ONLYPOSITIVE BEFORE INSERT OR UPDATE OF SAL ON EMP FOR EACH ROW
BEGIN
IF :NEW.SAL<0 THEN
--checks of the value inserted into teh sal column is less than 0.
RAISE_APPLICATI ON_ERROR(-20100,'PLEASE INSERT A POSITIVE VALUE');
--if valus is less than 0 ,then raise the exception.
END IF;
END;[/CODE]
Note:-Trigger does not allow negative values to insert/update to the table.
trigger example #3
=============== ===
[CODE=oracle]CREATE OR REPLACE TRIGGER NODELETE BEFORE INSERT OR UPDATE OR DELETE ON DEPT FOR EACH ROW
BEGIN
IF INSERTING THEN
RAISE_APPLICATI ON_ERROR(-20020,'CAN''T INSERT NEW RECORDS TO THIS TABLE');
ELSIF UPDATING THEN
RAISE_APPLICATI ON_ERROR(-20021,'CAN''T UPDATE RECORDS OF THIS TABLE');
ELSIF DELETING THEN
RAISE_APPLICATI ON_ERROR(-20022,'YOU ARE NOT AUTHORISED TO DELETE THESE RECORDS');
END IF;
END;[/CODE]
Note:- the triggermakes the trigger readonly by not allowing any DML on it.
Trigger Sample Ex #4
=============== =
[CODE=oracle]
create or replace trigger mytrig
before insert on emp
declare
a char(3);
begin
--selects the system day to the vaiable.
select to_char(sysdate ,'dy') into a from dual;
if a in('sun','sat') then
--restrict any transaction on saturday and sunday.
Raise_applicati on_error(-20004,'Cannot do manipulation today');
end if;
end;[/CODE]
Note:--This trigger restricts transactions on the table on weekends.
Also check TRIGGERS - 2