Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Murray_67
Starting Member
11 Posts |
Posted - 2005-10-15 : 09:15:13
|
I have created a trigger for an UPDATE to ChargeDetails. This trigger contains an INSERT INTO ChargeDetailsLogs FROM INSERTED rows. I also have a stored procedure that is EXEC from this trigger also. When I do a bulk update to ChargeDetails ex.UPDATE ChargeDetails SET PrintStatus = 'P' WHERE PrintStatus = 'T'I get subquery returned more than one row. What I basically would like this trigger to do is:If the only column updated is PrintStatus then do nothingIf PrintStatus and any other column(s) has been updated run this trigger.If PrintStatus was not updated and other column(s) have been updated run this trigger.Thanks for your HelpThe Trigger is below in bold.INSERT INTO ChargeDetailsLog(ChargeDetailID, ChargeHeaderID, InvoiceNum, CarrierID, TotalFee, Memo, NCAmt)SELECT ChargeDetailID, ChargeHeaderID, InvoiceNum, CarrierID, TotalFee, Memo, NCAmtFROM INSERTEDDECLARE @ChargeDetailID bigint,@CarrierID nvarchar(9), @NCAmt moneySET @ChargeDetailID = (SELECT ChargeDetail FROM INSERTED)SET @CarrierID = (SELECT CarrierID FROM INSERTED)SET @NCAmt = (SELECT NCAmt FROM INSERTED) EXEC spNCRemoval @ChargeDetailID,@CarrierID, @NCAmt Stored procedure is below:CREATE PROCEDURE spNCRemoval @ChargeDetailID bigint,@CarrierID nvarchar(9), @NCAmt moneyASIF @CarrierID IS NULL AND @NCAmt > 0 UPDATE ChargeDetails SET NCAmt = 0 WHERE ChargeDetailID = @ChargeDetailIDELSERETURNGOMurray |
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-10-15 : 09:45:09
|
Hi Murray,To test if only PrintStatus has been updated use columns_updated()--see Books Online for columns_updated section in CREATE TRIGGERie: this assumes PrintStatis is the 4th column in the tableif (columns_updated() = 8) returnA trigger for Insert and Update will fire ONCE for each insert or update statement. So if 100 rows were inserted the trigger still only fires once and the inserted table contains 100 rows. That's why you're getting the error:subquery returned more than one row. The select statement below is returning 100 different CarrierID valuesbut you're trying to assign them all to @CarrierID.SET @CarrierID = (SELECT CarrierID FROM INSERTED)You should move the called procedure logic into your trigger and process all the rows in inserted table as a set. Something like:if (columns_updated() = <columnBit> ) return--insert ChargeDetailsLogINSERT INTO ChargeDetailsLog(ChargeDetailID, ChargeHeaderID, InvoiceNum, CarrierID, TotalFee, Memo, NCAmt)SELECT ChargeDetailID, ChargeHeaderID, InvoiceNum, CarrierID, TotalFee, Memo, NCAmtFROM INSERTED--NCRemovalUPDATE cd set cd.NCAmt = 0from ChargeDetails cdjoin inserted i on cd.ChargeDetailID = i.ChargeDetailIDwhere i.NCAmt > 0 EDIT:I forgot to mention that if your update statements always includes all columns even if the values don't change then columns_updated() won't work. You'll need to compare individual column values between inserted and ChargeDetails table.Be One with the OptimizerTG |
|
|
Murray_67
Starting Member
11 Posts |
Posted - 2005-10-18 : 14:46:53
|
Thanks for the help. That workedMurray |
|
|
|
|
|
|
|