Instead of Update Trigger SQL Server 2000

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
barnetjebAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Computer101Connect With a Mentor Commented:
PAQed, with points refunded (500)

Computer101
E-E Admin
0
 
CarlovskiCommented:
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?
0
 
barnetjebAuthor 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?
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
CarlovskiCommented:
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!)
0
 
danblakeCommented:
Could you not move the data-requirement to a view -- so the view handles all updates/delets, then create the instead of trigger there ?
0
 
barnetjebAuthor 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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.