What are triggers in oracle ?
Oracle lets us define procedures called triggers that run implicitly when an
INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. These procedures can be written in PL/SQL or Java and stored in the database
Triggers are procedures that are stored in the database and implicitly run, or fired, when some event occurs.
SQL Statements Allowed in Trigger Bodies
The body of a trigger can contain DML SQL statements. It can also contain
SELECT statements, but they must be SELECT... INTO... statements or the SELECT statement in the definition of a cursor.
DDL statements are not allowed in the body of a trigger. Also, no transaction control statements are allowed in a trigger.
ROLLBACK, COMMIT, and SAVEPOINT cannot be used.For system triggers, {CREATE/ALTER/DROP} TABLE statements and ALTER...COMPILE are allowed.
Types of Triggers
Sample script of triggers:
create or replace trigger trigchk
before insert or update or delete
on emp
begin
if(to_char(sysdate,'DY') in ('SAT','SUN')) then
raise_application_error(-20101,'u r not allowed to do any transaction on weekend');
end if;
end;
-------------------------------------------------------------------------------------------------------
create or replace trigger trigsal
before update
on emp for each row
begin
if(:old.sal>:new.sal) then
raise_application_error(-20103,'nat a valid action');
end if;
end;
----------------------------------------------------------------------------
Create or replace trigger trigview
INSTEAD OF INSERT on vwempdept
for each row
begin
insert into dept(deptno,dname) values (seq.nextval,new.dname);
insert into emp(empno,ename,sal,deptno) values(:new.empno,:new.ename,
:new.sal,:new.deptno);
end;
Tags: Oracle Database, triggers
Subscribe to:
Post Comments (Atom)
Share your views...
0 Respones to "What are triggers in oracle ?"
Post a Comment