[RBG7-L] - Re: Triggers before and after

[Bernard Lis]

Thank you Razzak,
I shall try that tomorrow, when I am wider awake.
for now, Nighty Nite!  Sleep tight!
Bernie
----- Original Message ----- 
From: "A. Razzak Memon" <[EMAIL PROTECTED]>
To: "RBG7-L Mailing List" <[EMAIL PROTECTED]>
Sent: Monday, November 15, 2004 11:55 PM
Subject: [RBG7-L] - Re: Triggers before and after


At 11:04 PM 11/15/2004 -0500, Bernie Lis wrote:
alter table employee add trigger update demo3
when I list triggers, it shows up as an update trigger
alter table employee add trigger update after demo3b
list triggers and get:
update trigger: No Trigger Defined
After Update Trigger: demo3b
Why did the update trigger demo3  disappear?

Bernie,
When you use the ALTER TABLE command you must define
all TRIGGERS for that table in the same command.
In your case, if you need to add both UPDATE AND
UPDATE AFTER TRIGGERs, you'll need to define both
triggers in the same command.
Example 01:
ALTER TABLE Employee ADD TRIGGER +
UPDATE Demo3 UPDATE AFTER Demo3b
Example 02:
The following example defines all TRIGGERs:
Table: TrigTest
Insert Procedure: TrigBI
After Insert Procedure: TrigAI
Update Procedure: TrigBU
After Update Procedure: TrigAU
Delete Procedure: TrigBD
After Delete Procedure: TrigAD
Syntax:
ALTER TABLE TrigTest ADD TRIGGER +
INSERT TrigBI +
INSERT AFTER TrigAI +
UPDATE TrigBU +
UPDATE AFTER TrigAU +
DELETE TrigBD +
DELETE AFTER TrigAD
Notes:
01. A table can have both "BEFORE" and "AFTER" TRIGGERs,
    only one, or none.
02. Typical TRIGGER Usage:
    BEFORE- data validation before the action (inventory
    checks, account limit checks)
    AFTER- update of data dependent on primary keys,
    automated post transaction steps.
03. If you have defined the "AFTER" Trigger on INSERT,
    UPDATE, DELETE, then, the INSERT/UPDATE/DELETE MUST
    be successful for the AFTER Trigger to fire. Otherwise,
    ONLY the BEFORE Trigger (if defined) will fire regardless
    of the success or failure on INSERT/UPDATE/DELETE action.
Hope that helps!
Enjoy and make sure to have fun!
Very Best R:egards,
Razzak.
--- RBG7-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [EMAIL PROTECTED]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE: 
Send a plain text email to [EMAIL PROTECTED]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [EMAIL PROTECTED]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body, 
place any 
text to search for.
================================================