True, so don't do that. ----- Original Message ----- From: Albert Berry To: RBASE-L Mailing List Sent: Friday, April 11, 2008 10:28 AM Subject: [RBASE-L] - Re: Behavior of Before and After Triggers
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 >
