Good idea, Rachael, except that rules can be turned off. SET RULES OFF
and the delete will still take place despite the existence of the data.
Albert
Rachael Malberg wrote:
Morning Jim!
Not sure I follow the question but my brain is a little fuzzy...(could
be because it's Friday and I've got a bad case of golfing fever but
with 12" of snow still on the ground and 6-8" more to come today and
tomorrow, relief is a little ways off.).. but are you trying to
prevent the deletion of a master record (a member) if a
particular child record (contribution history) exists?
If so I don't think a trigger is the correct solution, how about a
restricting deletion rule on the Member table 'where MemberPK in
(select MemberFK from ContributionHistory)' ?
Have a Fabulous Day!
Rachael M.
Freelance Developer
www.DragonflyDevelopmentMN.com <http://www.DragonflyDevelopmentMN.com>
----- Original Message -----
*From:* James Bentley <mailto:[EMAIL PROTECTED]>
*To:* RBASE-L Mailing List <mailto:[email protected]>
*Sent:* Thursday, April 10, 2008 10:54 PM
*Subject:* [RBASE-L] - Behavior of Before and After Triggers
I need some clarification of about "ABORT TRIGGER." RBase Help
states "A trigger automatically runs a stored procedure when an
UPDATE, DELETE, or INSERT command is run with a table. Since a
trigger runs a stored procedure before the row that triggered it
is updated, inserted, or deleted, you can cancel the update,
insert, or delete with the ABORT TRIGGER command in the stored
procedure. Also, you can verify the action being performed in an
update trigger on the row by using a SELECT command with the
WHERE CURRENT OF SYS_OLD/SYS_NEW syntax to check the row
before/after the update." The Tipoftheday.pdf also states "5.2
Order of Processing for Stored Procedures and Triggers
A trigger automatically runs a stored procedure when an UPDATE,
DELETE, or INSERT command is run with a
table.
The following describes the sequence in which data integrity is
maintained when using Stored Procedures and
Triggers:
DELETE
· Rules
· Cascades
· Triggers
· Keys
· Row itself
UPDATE
· Rules
· Cascades
· Triggers
· Keys
· Row itself
INSERT
· Rules
· Triggers
· Keys
· Row itself
Triggers automatically run a stored procedure when an UPDATE,
DELETE, or INSERT command is run with a
table. The trigger is run before the row that triggered it is
updated, inserted, or deleted.
Since the trigger runs first, it gives you a chance to abort the
procedure.
When indexes are updated it is the last step before completing
the update.
The row has valid values, the rules are met, and the triggers
did not abort.
The only error out at this point is if the key update fails,
such as a PKFK violation. When the key processing
code is done it is a real attempt to update the key, not just a
"will it be ok" process. If you do not want your
triggers to run you could modify the trigger to check for PK-FK
violations before doing the rest of the trigger."
Triggers can be defined as TRIGGER BEFORE DELETE and/or TRIGGER
AFTER DELETE. If I have a before delete and an after delete
trigger and issue an ABORT TRIGGER in the TRIGGER BEFORE DELETE
will the TRIGGER AFTER DELETE still execute.
The reason for my question is I have a master member table
Having CASCADE BOTH with many sub-tables. Normally you would
never delete a record from the master table. There are some
exceptions and one sub-table the "contribution history" is
critical and I don't want to allow a delete if contribution
records exist. I want to issue an ABORT TRIGGER in the TRIGGER
BEFORE DELETE procedure and also not execute a needed TRIGGER
AFTER DELETE procedure.
Any insight would be appreciated.
Jim Bentley
American Celiac Society
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
tel: 1-504-737-3293
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
