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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Trigger

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 nothing
If 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 Help
The Trigger is below in bold.
INSERT INTO ChargeDetailsLog
(ChargeDetailID, ChargeHeaderID, InvoiceNum, CarrierID, TotalFee, Memo, NCAmt)
SELECT ChargeDetailID, ChargeHeaderID, InvoiceNum, CarrierID, TotalFee, Memo, NCAmt
FROM INSERTED
DECLARE @ChargeDetailID bigint,@CarrierID nvarchar(9), @NCAmt money
SET @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 money
AS
IF @CarrierID IS NULL AND @NCAmt > 0
UPDATE ChargeDetails
SET NCAmt = 0
WHERE ChargeDetailID = @ChargeDetailID
ELSE
RETURN
GO



Murray

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 TRIGGER
ie: this assumes PrintStatis is the 4th column in the table
if (columns_updated() = 8)
return


A 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 values
but 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 ChargeDetailsLog
INSERT INTO ChargeDetailsLog
(ChargeDetailID, ChargeHeaderID, InvoiceNum, CarrierID, TotalFee, Memo, NCAmt)
SELECT ChargeDetailID, ChargeHeaderID, InvoiceNum, CarrierID, TotalFee, Memo, NCAmt
FROM INSERTED

--NCRemoval
UPDATE cd set
cd.NCAmt = 0
from ChargeDetails cd
join inserted i on cd.ChargeDetailID = i.ChargeDetailID
where 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 Optimizer
TG
Go to Top of Page

Murray_67
Starting Member

11 Posts

Posted - 2005-10-18 : 14:46:53
Thanks for the help. That worked

Murray
Go to Top of Page
   

- Advertisement -