SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Murray_67
Starting Member

USA
11 Posts

Posted - 10/15/2005 :  09:15:13  Show Profile  Send Murray_67 an AOL message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 10/15/2005 :  09:45:09  Show Profile  Reply with Quote
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

Edited by - TG on 10/15/2005 09:51:01
Go to Top of Page

Murray_67
Starting Member

USA
11 Posts

Posted - 10/18/2005 :  14:46:53  Show Profile  Send Murray_67 an AOL message  Reply with Quote
Thanks for the help. That worked

Murray
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000