June 1, 2009 at 9:22 am
I need to create a trigger in a table that contain 104 columns and I just need to keep the changes for Update and delete in the Primary Key and when GLPOST='P'
The columns [GLDCT],[GLDOC],[GLKCO],[GLDGJ],[GLJELN],[GLLT],[GLEXTL] are my PK
and when the column GLPOST='P'
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [F0911_UPDATE]ON [CRPDTA].[F0911]
FOR UPDATE
AS
INSERT INTO audit.PRODUSA.F0911_AUDIT
Select 'UPDATE','BEFORE',current_timestamp,SUBSTRING(system_user, CHARINDEX('\', system_user) + 1, LEN(system_user)),SUBSTRING(session_user, CHARINDEX('\', session_user) + 1, LEN(session_user)), null,
[GLDCT],[GLDOC],[GLKCO],[GLDGJ],[GLJELN],[GLLT],[GLEXTL]
from deleted
INSERT INTO audit.PRODUSA.F0911_AUDIT
Select 'UPDATE','AFTER',current_timestamp,SUBSTRING(system_user, CHARINDEX('\', system_user) + 1, LEN(system_user)),SUBSTRING(session_user, CHARINDEX('\', session_user) + 1, LEN(session_user)), null,
[GLDCT],[GLDOC],[GLKCO],[GLDGJ],[GLJELN],[GLLT],[GLEXTL]
from INSERTED
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
June 2, 2009 at 2:23 pm
I suggest using the "update(column)" function inside the trigger to check for changes.
Details found in BOL at: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/8e3be25b-2e3b-4d1f-a610-dcbbd8d72084.htm
Below I started an if statement to get you started.
ALTER TRIGGER [F0911_UPDATE]ON [CRPDTA].[F0911]
FOR UPDATE
AS
-- CHECK IF THE COLUMNS CHANGED, USE IF NOT((... to check that they didn't change
IF ((UPDATE(GLDCT) OR UPDATE(GLDOC) OR ...) AND GLPOST = 'P') BEGIN
INSERT INTO audit.PRODUSA.F0911_AUDIT
Select 'UPDATE','BEFORE',current_timestamp,SUBSTRING(system_user, CHARINDEX('\', system_user) + 1, LEN(system_user)),SUBSTRING(session_user, CHARINDEX('\', session_user) + 1, LEN(session_user)), null,[GLDCT],[GLDOC],[GLKCO],[GLDGJ],[GLJELN],[GLLT],[GLEXTL]
from deleted
INSERT INTO audit.PRODUSA.F0911_AUDIT
Select 'UPDATE','AFTER',current_timestamp,SUBSTRING(system_user, CHARINDEX('\', system_user) + 1, LEN(system_user)),SUBSTRING(session_user, CHARINDEX('\', session_user) + 1, LEN(session_user)), null,
[GLDCT],[GLDOC],[GLKCO],[GLDGJ],[GLJELN],[GLLT],[GLEXTL]
from INSERTED
END
GO
-
June 2, 2009 at 2:58 pm
You need to be careful when using IF UPDATE() because that will show even if the data has not changed.
Try this:
CREATE TABLE update_test
(
col1 VARCHAR(10),
col2 INT
)
CREATE TABLE update_Test_Log
(
col1_before VARCHAR(10),
col1_after VARCHAR(10)
)
Go
CREATE TRIGGER test ON update_test
AFTER UPDATE
AS
IF UPDATE(col1)
BEGIN
INSERT INTO update_Test_Log (
col1_before,
col1_after
)
Select
(SELECT col1 FROM deleted),
(SELECT col1 FROM inserted)
END
GO
BEGIN TRANSACTION
INSERT INTO update_test (
col1,
col2
)
SELECT
'Test',
1
COMMIT TRANSACTION
BEGIN TRANSACTION
UPDATE update_test
SET col1 = col1
COMMIT TRANSACTION
SELECT * FROM update_Test_Log AS UTL
Of course there is not much you can do about it if your primary key field is one you are updating as you cannot JOIN the inserted and deleted tables unless you have another unique index that is not affected by the update.
Do have Foreign Keys setup using the Primary Key column(s) of this table? If so do you have cascading updates enabled?
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply