We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Instead of Update Trigger SQL Server 2000

barnetjeb
barnetjeb asked
on
Medium Priority
388 Views
Last Modified: 2007-12-19
This is what I'm trying to do:

I have a table in which I update a record.  Simplified version of what I'm trying to do:

Table A
Product: Apples
Rate: $15
Date Start: 2004/01/01
Date End: 2004/12/31

Table B
Customer: George
Record Sequence Number: 1
Product Purchased: Apples
Rate: $15
Date Purchased: 2004/02/01

Somone comes along and decided that the prices of apples should only be $13.  I want to reverse all records.  In other words insert a sequence number 2 with the old rate negated and then insert a sequence 3 with the new rate.  This will allow George to get a credit of $2.

I get this error message on my trigger.  The table I'm doing the update on has a master table one level above it with cascaded delete and updates.  It does not have any child tables.

Error 2113:  Cannot CREATE INSTEAD OF DELETE or UPDATE TRIGGER becuase the table has a FOREIGN KEY with cascaded DELTE or UPDATE

Thanks
Comment
Watch Question

I'm afraid you just can't do it!
Makes sense really, you are saying 'If you a record from this table, delete all my child records' and then add a trigger saying 'If someone tries to delete me, do something else instead', it cannot really do the first.
You can have before and after (And instead of insert triggers for that matter) to your hearts content, which is what I think you want anyway, you should implemement this as an after update trigger.
Any way, the data model seems a bit odd, why are you recording the rate in table B anyway?

Author

Commented:
I thought sql server didn't allow before triggers?

My application is quite a bit more complex than the example I showed you.  I need to keep the rate with the record because information is transferred to another system.  Once payment occurs, the record needs to be reversed and then regenerated with the correct payment amount.  Both these new records are sent to another system.

Can you explain your comment.  I realize that an update is just a delete and insert.  Is there a way to do some stuff before the record is updated?  I just want to do some things when this record is updated.  I don't care about when the parent record is deleted.  Is there a way to get around this?
Sorry, you are right, no before triggers in SQL Server(Been using Oracle a bit too much recently!)!
I think you can do pretty much what you are currently trying in the instead of trigger , just put it into the after update trigger.
Or get rid of the cascade deletes and handle them yourself (With another trigger!)

Commented:
Could you not move the data-requirement to a view -- so the view handles all updates/delets, then create the instead of trigger there ?

Author

Commented:
Actually I ended up doing my before stuff within my code (vb.net) before I execute my update.  I don't want to remove my relationships.

Thanks for all the input
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.