July 17, 2014 at 6:59 am
Good Day
I am attempting something I haven't done before with a trigger, I am logging multi row inserts and updates on a table, the insert trigger works fine but I would like to only log an update if a particular column is updated in the table.
The trigger looks like such at the moment:
ALTER TRIGGER [dbo].[Trig_Log_UPDATE_OD_DETAIL]
ON [dbName].[dbo].
AFTER UPDATE
AS
BEGIN
if @@ROWCOUNT = 0
RETURN
SET NOCOUNT ON
INSERT INTO [dbName].[dbo].[log_table]
([DATEANDTIME], [ORDER_NUMBER], [USERNAME], [ORDER_PRIMARY], [ORDER_LINEDISC], [TYPE])
VALUES (GETDATE(),
(SELECT DELETED.OD_ORDER_NUMBER FROM DELETED),
SUSER_NAME(),
(SELECT DELETED.OD_PRIMARY FROM DELETED),
(SELECT ORD_DETAIL.OD_LINEDISC FROM ORD_DETAIL JOIN DELETED
ON DELETED.OD_PRIMARY = ORD_DETAIL.OD_PRIMARY),
'UPDATE'
)
END;
The column I am interested in is OD_LINEDISC
any help will be appreciated
July 17, 2014 at 8:13 am
The only way to do this is to compare the value in that column between the inserted and deleted tables. Here's how I'd do it:
INSERT INTO [dbName].[dbo].[log_table]
(
[DATEANDTIME],
[ORDER_NUMBER],
[USERNAME],
[ORDER_PRIMARY],
[ORDER_LINEDISC],
[TYPE]
)
SELECT
GETDATE(),
D.OD_ORDER_NUMBER,
SUSER_NAME(),
D.OD_PRIMARY,
D.OD_LINEDISC,
'UPDATE'
FROM
deleted AS D
WHERE
EXISTS ( SELECT
1
FROM
inserted AS I
WHERE
D.OD_PRIMARY = I.OD_PRIMARY AND
D.OD_LINEDISC <> I.OD_LINEDISC )
I'm assuming that OD_PRIMARY is the primary key/unique index and that you want to log the deleted value not the new value.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 17, 2014 at 8:42 am
OD_Primary is the PK but what we want to log is the new value, the value that will change is od_linedisc
The deleted table will have the old value in it but we want to know what the value has been changed to in the log.
Normally I would use something like: IF (Update (od_linedisc)
But that doesn't work in this instance
Hope that makes sense.
July 17, 2014 at 8:52 am
Okay. I don't really get logging the new value since the new value is always the value currently in the table. All you need to do is change the query to JOIN the deleted and inserted tables instead of doing the EXISTS and then use I.OD_LINEDISC.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply