?
Solved

Instead of Update Trigger SQL Server 2000

Posted on 2004-03-24
8
Medium Priority
?
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

765 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