Trigger Short Tutorial

posted on 15 Jul 2004 16:08 by somkiat

เพิ่งเคยเขียน  Trigger บน  Oracle ครับเลยเอามาฝาก

 

Triggers

Triggers are a special PL/SQL construct similar to procedures. However, a procedure is executed explicitly from another block via a procedure call, while a trigger is executed implicitly whenever the triggering event happens. The triggering event is either a INSERT, DELETE, or UPDATE command. The timing can be either BEFORE or AFTER. The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

Below is the syntax for creating a trigger in Oracle (this syntax has been simplified; for the complete version try HELP CREATE TRIGGER in sqlplus):

CREATE [OR REPLACE] TRIGGER <trigger_name>
    {BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>
    [FOR EACH ROW [WHEN (<trigger_condition>)]]
    <trigger_body>

Some important points to note:

  • You can create only BEFORE and AFTER triggers for tables. (INSTEAD OF triggers are only available for views; typically they are used to implement view updates.)
  • You may specify up to three triggering events using the keyword OR. Furthermore, UPDATE can be optionally followed by the keyword OF and a list of attribute(s) in <table_name>. If present, the OF clause defines the event to be only an update of the attribute(s) listed after OF. Here are some examples:
        ... INSERT ON R ...
        ... INSERT OR DELETE OR UPDATE ON R ...
        ... UPDATE OF A, B OR INSERT ON R ...
  • If FOR EACH ROW option is specified, the trigger is row-level; otherwise, the trigger is statement-level.
  • For a row-level trigger, a trigger restriction can be specified in the WHEN clause, enclosed by parentheses. The trigger restriction is a SQL condition that must be satisfied in order for Oracle to fire the trigger. This condition cannot contain subqueries. Without the WHEN clause, a trigger is fired by every triggering event.
  • <trigger_body> is a PL/SQL block, rather than sequence of SQL statements. Oracle has placed certain restrictions on what you can do in <trigger_body>, in order to avoid situations where one trigger performs an action that triggers a second trigger, which then triggers a third, and so on, which could potentially create an infinite loop. The restrictions on <trigger_body> include:
    • You cannot modify the same relation whose modification is the event triggering the trigger.
    • You cannot modify a relation connected to the triggering relation by another constraint such as a foreign-key constraint.

We illustrate Oracle's syntax for creating a trigger through an example based on the following two tables:

CREATE TABLE T4 (a INTEGER, b CHAR(10));
CREATE TABLE T5 (c CHAR(10), d INTEGER);

We create a trigger that may insert a tuple into T5 when a tuple is inserted into T4. Specifically, the trigger checks whether the new tuple has a first component 10 or less, and if so inserts the reverse tuple into T5:

CREATE TRIGGER trig1
    AFTER INSERT ON T4
    FOR EACH ROW
    WHEN (NEW.a <= 10)
    BEGIN
        INSERT INTO T5 VALUES(:NEW.b, :NEW.a);
    END trig1;
.
run;

The special variables NEW and OLD are available to refer to new and old tuples respectively. Note: In the trigger body, NEW and OLD must be preceded by a colon (":"), but in the WHEN clause, they do not have a preceding colon!

Again, notice that we end the CREATE TRIGGER statement with a dot and run, as for all PL/SQL statements in general. Running the CREATE TRIGGER statement only creates the trigger; it does not execute the trigger. Only a triggering event, such as an insertion into T4 in this example, causes the trigger to execute.

To view information about your triggers, use the following:

select trigger_name from user_triggers;

select trigger_type, table_name, triggering_event
from user_triggers
where trigger_name = '<trigger_name>';

To drop a trigger:

drop trigger <trigger_name>;

To disable or enable a trigger:

alter trigger <trigger_name> {disable|enable};

Comment

Comment:

Tweet

Holyoak juicy couture said louis vuitton,louis vuitton canada,louis vuitton outlet,louis vuitton outlet online beachings swarovski jewelry of polo ralph lauren juvenile salmon converse pas cher sharks air max aren't common, but louboutin,christian louboutin,louboutin shoes,louboutins,louboutin uk,christian louboutin uk,red bottom shoes,red bottoms,louboutin outlet,christian louboutin shoes,christian louboutin outlet they're not instyler ionic styler,instyler unusual on nike free,free running,nike free run,nike free 5.0,free running 2,nike running shoes,nike free trainer,nike free trainer 5.0,free runs,free run 5.0 the Oregon coach outlet,coach factory,coach factory outlet,coach outlet store,coach outlet store online,coach factory online,coach factory outlet online,coach outlet online coast. "Most of michael kors canada the burberry outlet online,burberry outlet,burberry,burberry handbags,burberry factory outlet,burberry sale time when oakley sunglasses cheap,cheap oakley sunglasses,oakley sunglasses,oakley vault,oakleys,oakley.com,sunglasses outlet,cheap oakley,oakley outlet,cheap sunglasses,oakley prescription glasses,fake oakleys,oakley sunglasses outlet,oakley glasses,oakley store,fake oakley,oakley sale,cheap oakleys,discount oakley sunglasses people moncler jackets see them on the beach, they've already swarovski uk died," oakley he nike free,free run,nike free run,nike free pas cher,nike free run pas cher,nike free france said.

 

Thankfully, hollister,hollister canada,abercrombie and fitch,abercrombie,abercrombie and,abercrombie kids,af he sac louis vuitton,louis vuitton,louis vuitton pas cher,sac louis vuitton pas cher,louis vuitton france was able burberry outlet online,burberry,burberry outlet,burberry handbags,burberry factory outlet,burberry sale to valentino shoes,valentino,valentinos get to nike blazer this shark before it cheap oakley was longchamp,longchamp bags,longchamp uk too late.Back louis vuitton outlet stores,louis vuitton outlet online,louis vuitton,louis vuitton outlet,louisvuitton.com,authentic louis vuitton,louis vuitton factory outlet,cheap louis vuitton in mcm handbags,mcm bags,mcm backpack,mcm outlet August 2012, karen millen dresses Alan Holyoak ugg,ugg australia,uggs,ugg pas cher,ugg soldes,bottes ugg,bottes ugg pas cher went to Seaside, Oregon, true religion for ray ban sunglasses uk his ray ban sunglasses,ray ban sunglasses outlet,ray ban,rayban,ray bans,ray ban outlet,ray-ban,raybans,ray ban wayfarer,ray-ban sunglasses,raybans.com,rayban sunglasses,cheap ray ban niece's wedding. He never expected that he'd sac longchamp,longchamp,longchamps,longchamp pas cher,sac longchamp pas cher,longchamp pliage,longchamp soldes,sac longchamps,longchamp france come back kate spade handbags,kate spade,kate spade outlet,katespade a hero.

 

Holyoak has a background canada goose outlet in iphone 6s cases marine ferragamo shoes,ferragamo,salvatore ferragamo,ferragamo belts,ferragamo belt,ferragamo outlet biology moncler and louis vuitton,louis vuitton bags,louis vuitton handbags,louis vuitton uk teaches at Brigham celine handbags,celine bag,celine bags Young ugg,ugg australia,ugg italia University. gucci Returning prada outlet,prada,prada handbags,prada sunglasses,prada shoes,prada bags from herve leger,herve leger dresses an lancel early converse shoes,converse,converse.com,converse sneakers,converse outlet walk lululemon outlet,lululemon,yoga pants,lulu lemon,lulu.com,lululemon.com one morning, moncler he and giuseppe zanotti his louis vuitton,louis vuitton outlet online,louis vuitton outlet,louisvuitton.com,authentic louis vuitton,louis vuitton factory outlet,cheap louis vuitton wife ugg boots,uggs,ugg,ugg boots uk,ugg uk saw abercrombie and fitch,abercrombie and,abercrombie,abercrombie kids,abercrombie fitch,abercrombie.com a person michael kors staring nike roshe uk at louis vuitton,borse louis vuitton,louis vuitton sito ufficiale,louis vuitton outlet an object lying just where canada goose outlet the waves break oakley sunglasses on links of london uk the moncler beach.

 

"I rolex watches,replica watches,omega watches,rolex watches for sale,replica watches uk,fake rolex couldn't north face tell michael kors outlet online what nike shoes,nike outlet,nike factory,nike store,nike factory outlet,nike outlet store,cheap nike shoes,nike sneakers it longchamp,longchamp handbags,longchamp outlet,longchamp bags,long champ was from polo ralph lauren outlet online,ralph lauren,polo ralph,polo ralph lauren,ralph lauren outlet,polo shirts,ralph lauren outlet online,polo ralph lauren outlet,ralphlauren.com,polo outlet,ralph lauren polo a mont blanc distance," timberland shoes Holyoak nike trainers told The Dodo. vans,vans pas cher,vans soldes "It was ray ban sunglasses outlet,ray ban sunglasses,ray ban,rayban,ray bans,ray ban outlet,ray-ban,raybans,ray ban wayfarer,ray-ban sunglasses,raybans.com,rayban sunglas