|
guna
Starting Member
1 Post |
 Posted - 2009-05-21 : 08:14:38
|
| HI,I have a trigger to capture the event actions into the table to capture the ddl operation(create,drop,alter). i want to change the datatype of the column in test table. as per my code the trigger will fire and this capture the ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME DATATYPE query. after execute this query i want to capture the older datatype from which the column datype changed. for example int to decimal....i want to display as int to decimal.....IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EventLogNew]'))--AND type in (N'PK', N'PC'))--GO CREATE TABLE EventLogNew ( ID INT IDENTITY(1,1) NOT NULL, EventType NVARCHAR(100), PostTime DATETIME, SPID INT, ServerName NVARCHAR(100), LoginName NVARCHAR(100), UserName NVARCHAR(100), DatabaseName NVARCHAR(100), SchemaName NVARCHAR(100), ObjectName NVARCHAR(100), ObjectType NVARCHAR(100), TSQLCommand NVARCHAR(2000) ) GO IF EXISTS (SELECT * FROM sys.triggers WHERE name = N'DB_trPrevent' AND parent_class=0) BEGIN DROP TRIGGER [DB_trPrevent] ON DATABASE END GO CREATE TRIGGER DB_trPrevent ON DATABASE FOR ALTER_TABLE,CREATE_TABLE,DROP_TABLE,ALTER_VIEW,CREATE_VIEW,DROP_VIEW,ALTER_PROCEDURE,CREATE_PROCEDURE,DROP_PROCEDURE AS DECLARE @Data XML SET @Data = EventData() INSERT EventLogNew(EventType, PostTime, SPID, ServerName, LoginName, UserName, DatabaseName, SchemaName, ObjectName, ObjectType, TSQLCommand) VALUES( @Data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), GETDATE(), @Data.value('(/EVENT_INSTANCE/SPID)[1]', 'int'), @Data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(100)'), --CONVERT(NVARCHAR(100), CURRENT_USER), @Data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)'), @Data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)'), @Data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'), @Data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(100)'), @Data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)'), @Data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)'), @Data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')) GO |
|
can i capture the column datatype