May 31, 2002 at 11:21 am
Hi there,
I have a big question, I'm programming triggers in my sql server 2000 database, my question is, in sql 2000 is it possible to use a "before delete" in the trigger ?, 'cause I need to save some data before deleting the record.
I hope someone there can answer to me, thanks a lot,
Amaury Coria,
L.I. Amaury C.R.
May 31, 2002 at 12:09 pm
Sure. Even in SQL7 a standard "after" trigger gives you the logical deleted table which contains all the info from the deleted rows except text columns. In SQL2K you can use an instead of trigger to capture even the text columns or do something other than the actual delete if you wish.
Andy
May 31, 2002 at 8:40 pm
OK Andy,
Thanks a lot for answering to me, just one more question, how works an instead of trigger? is it as a before delete trigger?, actually my problem or question is that for example in Interbase 6 I have both options, "before delete" and "after delete", so then the instead trigger how works ?
Excuse me, maybe I'm still a little confused, my actual database is in Interbase 6, and my new designing is in SQL2000, that's why now I'm finding some details in SQL, as these...I know everything about the deleted table and the inserted table, and in general how triggers works.
Thanks a lot for your support,
Amaury
L.I. Amaury C.R.
May 31, 2002 at 9:38 pm
Well, it could be a before trigger - depends on how you could it. An instead of trigger fires before the action actually completes. That gives you a chance to look at the data inside the scope of the trigger and do whatever you want - roll it back, continue it, or potentially even do something different based on the data. A great example of instead of triggers is on a view that would be otherwise not updateable, you can redirect the update to the appropriate underlying data.
So in the case of an instead of delete trigger firing, you actually have to issue ANOTHER delete statement inside the trigger if you really want the rows to be deleted!
Andy
June 1, 2002 at 11:19 am
I guess it boils down to what you are needing to do as to how we best answer your question. Can you give us a bit more detail please?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 5, 2002 at 7:13 am
Thanks a lot for your suggestions and advices, they are very useful,
Well it's a long story, I have 2 tables, one is general, one is detailed, when I have a new record in the detailed one, I have to save somewhere else the data and affect the general table, why?, because, later I can enter a new record in the detailed table, affect the general one again overwriting, but if the user mistakes in this new record I have to look for the previous one record and give back the data, I have a trigger, and at the beginning I thought about having a before delete trigger for saving data, but now I changed my mind, now when I have the 1st record I save it into a table, later the user enters the new record, if mistakes I have the data in the table where I previously saved it. It's long...
Thanks a lot again, you're very very kind
L.I. Amaury C.R.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply