use master go CREATE TRIGGER Login_Check_Trigger ON all server AFTER LOGoN AS BEGIN DECLARE @ip varchar(20) DECLARE @ipmac varchar(20) DECLARE @computer varchar(20) DECLARE @count INT DECLARE @iptime datetime SELECT @ipmac = net_address,@computer = hostname,@iptime = login_time,@ip = client_net_address FROM sys.dm_exec_connections,sys.sysprocesses where sys.dm_exec_connections.session_id = sys.sysprocesses.spid select @count = count(computer) from ipmac where computer = @computer and ipmac = @ipmac and delt<>'1' if(@count < 1) begin INSERT INTO ip_er(ip,ipmac,computer,iptime) VALUES (@ip,@ipmac,@computer,@iptime); rollback tran end
END GO 在判断计数是否大于1,后的语句中,加了回滚事件“rollback tran”后,数据不写插入到表中。各位帮我看下,这是怎么回事。
------解决方案-------------------- 回滚了当然没有写入的操作了
------解决方案-------------------- 如果有不满足if(@count < 1)条件的就回滚了
------解决方案--------------------
应该是根本没有跑进这个分支里, if(@count < 1) begin ... end
------解决方案-------------------- 从程序逻辑看,这句应该这么写才对,
SQL code
SELECT @ipmac = net_address,@computer = hostname,@iptime = login_time,@ip = client_net_address
FROM sys.dm_exec_connections,sys.sysprocesses
where sys.dm_exec_connections.session_id = sys.sysprocesses.spid and sys.sysprocesses.spid=@@spid
------解决方案-------------------- if(@count < 1) begin INSERT INTO ip_er(ip,ipmac,computer,iptime) VALUES (@ip,@ipmac,@computer,@iptime); rollback tran end
if(@count < 1)
begin
rollback tran
INSERT INTO ip_er(ip,ipmac,computer,iptime)
VALUES (@ip,@ipmac,@computer,@iptime);
end
------解决方案-------------------- +1