Solved

Instead of Update Trigger SQL Server 2000

Posted on 2004-03-24
8
329 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
0
Comment
Question by:barnetjeb
8 Comments
 
LVL 2

Expert Comment

by:Carlovski
ID: 10668494
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
 

Author Comment

by:barnetjeb
ID: 10669449
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
 
LVL 2

Expert Comment

by:Carlovski
ID: 10669602
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Expert Comment

by:danblake
ID: 10676432
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
 

Author Comment

by:barnetjeb
ID: 10713215
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 11054411
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now