新建一个update触发器
说明:
(1)table1的表中在insert数据,insert的数据中有的数据过一段时间starttime也在update
(2) 如果表table1中数据Severity>=3并且starttime已update,就向table2插入数据,插入字段得值为ip, hostname,field1,Severity,starttime
当向table1表insert数据,有的数据可触发insert到table2,但有的数据insert到table1时提示下面的错误:
Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression.
请问如何修改?
create trigger trigger_update
on table1
for update
as
DECLARE @newip varchar(64)
DECLARE @newhostname varchar(64)
DECLARE @newfield1 varchar(64)
DECLARE @newfield2 varchar(64)
DECLARE @newSeverity NUMERIC(9,0)
DECLARE @newstarttime datetime
DECLARE @newserial NUMERIC(9,0)
if(update(starttime))
if (select table1.Severity from table1,inserted where table1.serial=inserted.serial)>=3
BEGIN
select @newip=ip, @newhostname=hostname,@newfield1=field1,@newfield2=field2,
@newSeverity=Severity,@newstarttime=starttime,@newserial=serial
FROM inserted
INSERT INTO table2
VALUES (@newip, @newhostname,@newfield1, @newSeverity,@newstarttime,@newserial)