我先创建了一个数据表用于保存DDL事件的表
create table auditlog
(
id int identity (1,1) primary key,
Command nvarchar(1000),
PostTime nvarchar(24),
HostName nvarchar(100),
LoginName nvarchar(100)
)
然后创建DDL触发器
create trigger audit
on database for ddl_database_level_events
as
declare @data xml
declare @cmd nvarchar(1000)
declare @posttime nvarchar(24)
declare @spid nvarchar(6)
declare @loginname nvarchar(100)
declare @hostname nvarchar(100)
set @data = eventdata()
set @cmd = @data.value('(/event_instance/tsqlcommand/commandtext)','nvarchar(1000)')
set @cmd = ltrim(rtrim(replace(@cmd,'','')))
set @posttime = @data.value ('(/event_instance/posttime)','nvarchar(24)')
set @spid = @data.value('(/event_instance/loginname)','nvarchar(100)')
set @hostname = host_name()
insert into auditlog (Command,PostTime,HostName,LoginName)
values (@cmd,@posttime,@hostname,@loginname)
go
但是出现消息 2389,级别 16,状态 1,过程 audit,第 11 行
XQuery [value()]: 'value()' 需要单独的操作数(或空序列),但找到 'xdt:untypedAtomic *' 类型的操作数
如何解决,请各位大虾帮帮忙
create table auditlog
(
id int identity (1,1) primary key,
Command nvarchar(1000),
PostTime nvarchar(24),
HostName nvarchar(100),
LoginName nvarchar(100)
)
然后创建DDL触发器
create trigger audit
on database for ddl_database_level_events
as
declare @data xml
declare @cmd nvarchar(1000)
declare @posttime nvarchar(24)
declare @spid nvarchar(6)
declare @loginname nvarchar(100)
declare @hostname nvarchar(100)
set @data = eventdata()
set @cmd = @data.value('(/event_instance/tsqlcommand/commandtext)','nvarchar(1000)')
set @cmd = ltrim(rtrim(replace(@cmd,'','')))
set @posttime = @data.value ('(/event_instance/posttime)','nvarchar(24)')
set @spid = @data.value('(/event_instance/loginname)','nvarchar(100)')
set @hostname = host_name()
insert into auditlog (Command,PostTime,HostName,LoginName)
values (@cmd,@posttime,@hostname,@loginname)
go
但是出现消息 2389,级别 16,状态 1,过程 audit,第 11 行
XQuery [value()]: 'value()' 需要单独的操作数(或空序列),但找到 'xdt:untypedAtomic *' 类型的操作数
如何解决,请各位大虾帮帮忙
set @data=EVENTDATA()
set @[email protected]('(/EVEVE_INSTANCE/TSQLCommand)[1]','Nvarchar(1000)')
set @cmd=Ltrim(RTRIM(replace(@cmd,' ','')))
SET @posttime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','NVARCHAR(24)')
set @[email protected]('(/event_instance/SPID)[1]','Nvarchar(6)')
这样可以可以正确创建和触发,但是command的值总是取到NULL,MS帮助里写法:
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )持续研究中,希望对你有帮助!