Doubt in DML Trigger - Regarding.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Doubt in DML Trigger - Regarding.

Author

Topic

maheshmca04
Starting Member

1 Post

Posted - 2008-12-01 : 09:04:26

Hai,

We used the below DDL Trigger during the table alter and we are able to get the T-SQL command/ statement from eventdata().

ALTER TRIGGER trPreventTblChange
ON DATABASE
FOR ALTER_TABLE
AS
DECLARE @Data XML
SET @Data = EventData()
INSERT EvtLog (PostTime, LoginName, EventType, TSQLCommand,Data)
VALUES
(GETDATE(),
CONVERT(NVARCHAR(100), CURRENT_USER),
@Data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@Data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),CONVERT(NVARCHAR(4000),@Data) ) ;
GO

Is there any method to get the T-SQL command/ statement using DML triggers?.

Ie. While we insert a record using INSERT Statement, is there any possible way to get the T-SQL Statement using DML trigger for AFTER INSERT

Thanks in advance

Regards,
K.A.Maheshkumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:09:07

try like below


ALTER TRIGGER trPreventTblChange
ON DATABASE
FOR ALTER_TABLE
AS
DECLARE @Data XML
SET @Data = EventData()
INSERT EvtLog (PostTime, LoginName, EventType, TSQLCommand,Data)
SELECT GETDATE(),
CONVERT(NVARCHAR(100), CURRENT_USER),
t.v.value('(./EventType)[1]', 'nvarchar(100)'),
t.v.value('(./TSQLCommand)[1]', 'nvarchar(2000)'),
CONVERT(NVARCHAR(4000),@Data) 
FROM @Data.nodes('/EVENT_INSTANCE') t(v)
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources