Solved

Instead of Update Trigger SQL Server 2000

Posted on 2004-03-24
8
343 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

839 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