October 15, 2005 at 7:24 am
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
October 16, 2005 at 9:55 am
Some quirks with triggers:
1. Triggers are invoked based on the statement but the statment may or may not have update any rows. For example, this statement will cause the update trigger to run, but no rows are updated.
UPDATE ChargeDetails
SET PrintStatus = 'P'
WHERE 1 = 2 -- Always false
This SQL statement then should be included at the start of the trigger to exit when zero rows are affected.
IF 0 = ( select count(*) FROM inserted )
RETURN
2. The UPDATE(column_name) will return true when the column is referenced in the update statement but it does not indicate if the value is changed. To determine if the values are changed and to include when a column is changed from not null to null or vis versa, this logic needs to be included to compare the column between the inserted and deleted tables:
FROMinserted
joindeleted
on inserted. = deleted.
AND(inserted.!= deleted.
-- Check for changes to NOT NULL from NULL
OR(inserted. is not null and deleted. is null)
-- Check for changes to NULL from NOT NULL
ORinserted.is null and deleted. is not null)
3. As the inserted and deleted tables cannot be joined if the primary key value has been changed, updates to any of the primary key columns must be disallowed. An error message and statement termination will be caused.
IF UPDATE()
BEGIN
RAISERROR(' value cannot be changed',16,1)
ROLLBACK
RETURN
END
4. If an AFTER trigger updates the table being updated, then recursion will occur and the update trigger will be invoked multiple times. To prevent this, self updates are coded in "instead of" triggers and not in after triggers.
Here are the resulting triggers:
create trigger ChargeDetails_tui_001
on ChargeDetails for INSTEAD OF update
-- Trigger Update Instead of with Sequence First
as
SET NOCOUNT ON
SET XACT_ABORT ON
-- Trigger is invoked by update but no rows may have been updated
IF 0 = ( select count(*) FROM inserted ) RETURN
--Primary key may not be updated
IF UPDATE(ChargeDetailID)
BEGIN
RAISERROR('ChargeDetailID value cannot be changed',16,1)
ROLLBACK
RETURN
END
UPDATEChargeDetails
SETChargeHeaderID= INSERTED.ChargeHeaderID
, InvoiceNum= INSERTED.InvoiceNum
, CarrierID= INSERTED.CarrierID
, TotalFee= INSERTED.TotalFee
, Memo= INSERTED.Memo
, NCAmt
= CASE WHEN INSERTED.CarrierID is null then 0 else INSERTED.NCAmt end
,PrintStatus= INSERTED.PrintStatus
FROMinserted
joindeleted
on inserted.ChargeDetailID = deleted.ChargeDetailID
AND(inserted.ChargeHeaderID!= deleted.ChargeHeaderID
ORinserted.InvoiceNum!= deleted.InvoiceNum
or inserted.TotalFee!= deleted.TotalFee
or inserted.Memo!= deleted.Memo
or inserted.NCAmt!= deleted.NCAmt
-- Check for changes to NOT NULL from NULL
OR(inserted.ChargeHeaderID is not null and deleted.ChargeHeaderID is null)
OR(inserted.InvoiceNum is not null and deleted.InvoiceNum is null)
OR(inserted.TotalFee is not null and deleted.TotalFee is null)
OR(inserted.Memo is not null and deleted.Memo is null)
OR(inserted.NCAmt is not null and deleted.NCAmt is null)
-- Check for changes to NULL from NOT NULL
ORinserted.ChargeHeaderIDis null and deleted.ChargeHeaderID is not null)
OR(inserted.InvoiceNum is null and deleted.InvoiceNum is not null)
OR(inserted.TotalFee is null and deleted.TotalFee is not null)
OR(inserted.Memo is null and deleted.Memo is not null)
OR(inserted.NCAmt is null and deleted.NCAmt is not null)
)
RETURN
GO
create trigger ChargeDetails_tua_001
on ChargeDetails for after update
-- Trigger Update AFter with Sequence First
as
SET NOCOUNT ON
SET XACT_ABORT ON
-- Trigger is invoked by update but no rows may have been updated
IF 0 = ( select count(*) FROM inserted )RETURN
--Primary key may not be updated
IF UPDATE(ChargeDetailID)
BEGIN
RAISERROR('ChargeDetailID value cannot be changed',16,1)
ROLLBACK
RETURN
END
-- Specification: If the only column updated is PrintStatus then do nothing
-- Logic: Check for any column updated except PrintStatus
INSERT INTO ChargeDetailsLog
(ChargeDetailID, ChargeHeaderID, InvoiceNum, CarrierID, TotalFee, Memo, NCAmt)
SELECT INSERTED.ChargeDetailID, INSERTED.ChargeHeaderID
, INSERTED.InvoiceNum, INSERTED.CarrierID, INSERTED.TotalFee, INSERTED.Memo, INSERTED.NCAmt
FROMinserted
joindeleted
on inserted.ChargeDetailID = deleted.ChargeDetailID
AND(inserted.ChargeHeaderID!= deleted.ChargeHeaderID
ORinserted.InvoiceNum!= deleted.InvoiceNum
or inserted.TotalFee!= deleted.TotalFee
or inserted.Memo!= deleted.Memo
or inserted.NCAmt!= deleted.NCAmt
-- Check for changes to NOT NULL from NULL
OR(inserted.ChargeHeaderID is not null and deleted.ChargeHeaderID is null)
OR(inserted.InvoiceNum is not null and deleted.InvoiceNum is null)
OR(inserted.TotalFee is not null and deleted.TotalFee is null)
OR(inserted.Memo is not null and deleted.Memo is null)
OR(inserted.NCAmt is not null and deleted.NCAmt is null)
-- Check for changes to NULL from NOT NULL
ORinserted.ChargeHeaderIDis null and deleted.ChargeHeaderID is not null)
OR(inserted.InvoiceNum is null and deleted.InvoiceNum is not null)
OR(inserted.TotalFee is null and deleted.TotalFee is not null)
OR(inserted.Memo is null and deleted.Memo is not null)
OR(inserted.NCAmt is null and deleted.NCAmt is not null)
)
RETURN
GO
SQL = Scarcely Qualifies as a Language
October 26, 2005 at 10:09 am
Thanks alot for the help Carl that worked great
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply