create table t1(c1 char(1))
create table t2(s varchar(1000))
go
create trigger tr_test
on database
for alter_table
as
insert t2(s) select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(1000)')
go
alter table t1
alter column c1 char(2)
go
select * from t2
go
drop table t2
drop table t1
drop trigger tr_test
go
/**
s
---------------
alter table t1 alter column c1 char(2)
**/
--帮助上的例子
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'ddl_trig_login')
DROP TRIGGER ddl_trig_login
ON ALL SERVER
GO
CREATE TRIGGER ddl_trig_login
ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
PRINT 'Login Event Issued.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_login
ON ALL SERVER
GO
create trigger tr_test
on database
for create_table
as
select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','varchar(1000)')
ROLLBACK;
执行:
use log_test
Create table wavetop(ID INT, NAME VARCHAR(20) not null);
GO结果是:
(1 行受影响)
消息 3609,级别 16,状态 2,第 2 行
事务在触发器中结束。批处理已中止。没有捕获SQL 语句.....希望两位兄弟帮我看看.....
Msg 1098, Level 15, State 1, Procedure tr_test, Line 5
The specified event type(s) is/are not valid on the specified target object.