June 19, 2019 at 11:17 pm
Hi.
I create a update trigger on a table to audit data changes...
the code used is:
ALTER TRIGGER dbo.Tg_table_upd ON dbo.table
FOR UPDATE
AS
DECLARE @DATA DATETIME= GETDATE();
DECLARE @USER VARCHAR(20);
SELECT
@USER = CONVERT(VARCHAR, context_info)
FROM master..sysprocesses
WHERE spid = @@SPID;
INSERT INTO table_LOG
SELECT
*,
@USER,
@DATA,
0
FROM deleted;
END;
I run several tests to check if the trigger was working and apparently its does his work fine....
if I update 1 or more rows in table...the old values are inserted into the audit table (table_log)
BUT....
In one case no records are been stored in audit table.
I run a SQL Profiler to capture what was coming to the server...
when a specific update is executed (captured from SQL profiler) from a java app:
DECLARE @P1 INT;
SET @P1 = 68235;
EXEC sp_prepexec
@P1 OUTPUT,
N'@P0 nvarchar(4000),@P1 bigint,@P2 bigint,@P3 bigint,@P4 bigint,@P5 bigint,@P6 bigint,@P7 bigint,@P8 bigint,@P9 bigint,@P10 bigint,@P11 bigint,@P12 bigint,@P13 bigint,@P14 bigint,@P15 bigint,@P16 bigint,@P17 bigint,@P18 bigint,@P19 bigint,@P20 bigint,@P21 bigint,@P22 bigint,@P23 bigint,@P24 bigint,@P25 bigint,@P26 bigint,@P27 bigint,@P28 bigint,@P29 bigint,@P30 nvarchar(4000)',
N'update table set irc_status=@P0 where (irc_id in (@P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , @P12 , @P13 , @P14 , @P15 , @P16 , @P17 , @P18 , @P19 , @P20 , @P21 , @P22 , @P23 , @P24 , @P25 , @P26 , @P27 , @P28 , @P29)) and irc_status=@P30 ',
N'XXX',
58074,
58079,
58082,
58086,
58091,
58095,
57880,
57879,
57874,
57873,
57953,
57959,
57954,
57960,
57785,
57790,
57779,
57784,
59113,
59110,
59116,
59119,
75840,
75851,
75843,
75852,
75834,
75844,
75842,
N'YYY';
SELECT
@P1;
The table dbo.Table is updated (10 row affected)... the trigger IS dispared...But (captured by sql profiler), when the code make the select in the DELETED logical table
SELECT
*,
@USER,
@DATA,
0
FROM deleted;
Zero rows are returned...
So...the update command run successfully(10 rows affected) ...the trigger runs...but "deleted" table inside the trigger is empty...
Just remembering: in others updates commands the "deleted" logical table has records and everything goes fine... in this specific update command... its empty... and I get no rows in my audit table
any thoughts?
Thanks in advance
June 21, 2019 at 12:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply