我的触发器是这样写的,应改怎么改?
CREATE TRIGGER tr_TABLE_Update ON TABLE
for update
AS
begin
DECLARE
@FIELD VARCHAR(100)
,@U_FieldName varchar(100)
,@U_OldValue varchar(1000)
,@U_NewValue varchar(1000)
IF UPDATE(PRD_NO)
BEGIN
SET @FIELD = (SELECT FIELD1 FROM deleted)
SET @U_FieldName = 'FIELD1'
SET @U_FieldChina = '货品代号'
SET @U_OldValue = (SELECT FIELD1 FROM deleted)
SET @U_NewValue = (SELECT FIELD1 FROM inserted)
INSERT INTO TABLE2(F1, FN, OV, NV) VALUES(@FIELD, @U_FIELDNAME, @U_OLDVALUE, @U_NEWVALUE)
END IF UPDATE(IDX1)
BEGIN
SET @FIELD = (SELECT FIELD1 FROM inserted)
SET @U_FieldName = 'FIELD2'
SET @U_OldValue = (SELECT FIELD2 FROM deleted)
SET @U_NewValue = (SELECT FIELD2 FROM inserted)
INSERT INTO TABLE2(F1, FN, OV, NV) VALUES(@FIELD, @U_FIELDNAME, @U_OLDVALUE, @U_NEWVALUE)
END
END
CREATE TRIGGER tr_TABLE_Update ON TABLE
for update
AS
begin
DECLARE
@FIELD VARCHAR(100)
,@U_FieldName varchar(100)
,@U_OldValue varchar(1000)
,@U_NewValue varchar(1000)
IF UPDATE(PRD_NO)
BEGIN
SET @FIELD = (SELECT FIELD1 FROM deleted)
SET @U_FieldName = 'FIELD1'
SET @U_FieldChina = '货品代号'
SET @U_OldValue = (SELECT FIELD1 FROM deleted)
SET @U_NewValue = (SELECT FIELD1 FROM inserted)
INSERT INTO TABLE2(F1, FN, OV, NV) VALUES(@FIELD, @U_FIELDNAME, @U_OLDVALUE, @U_NEWVALUE)
END IF UPDATE(IDX1)
BEGIN
SET @FIELD = (SELECT FIELD1 FROM inserted)
SET @U_FieldName = 'FIELD2'
SET @U_OldValue = (SELECT FIELD2 FROM deleted)
SET @U_NewValue = (SELECT FIELD2 FROM inserted)
INSERT INTO TABLE2(F1, FN, OV, NV) VALUES(@FIELD, @U_FIELDNAME, @U_OLDVALUE, @U_NEWVALUE)
END
END
CREATE TRIGGER tr_TABLE_Update ON TABLE
for update
AS
begin
DECLARE
@FIELD VARCHAR(100)
,@U_FieldName varchar(100)
,@U_OldValue varchar(1000)
,@U_NewValue varchar(1000)
IF UPDATE(FIELD1)
BEGIN
SET @FIELD = (SELECT FIELD1 FROM deleted)
SET @U_FieldName = 'FIELD1'
SET @U_OldValue = (SELECT FIELD1 FROM deleted)
SET @U_NewValue = (SELECT FIELD1 FROM inserted)
END IF UPDATE(FIELD2)
BEGIN
SET @FIELD = (SELECT FIELD1 FROM inserted)
SET @U_FieldName = 'FIELD2'
SET @U_OldValue = (SELECT FIELD2 FROM deleted)
SET @U_NewValue = (SELECT FIELD2 FROM inserted)
END INSERT INTO TABLE2(F1, FN, OV, NV) VALUES(@FIELD, @U_FIELDNAME, @U_OLDVALUE, @U_NEWVALUE)END
因为你一有记录而那个条件又没有清零的语句,
你应该在有的时候在WHILE里面就清零。
CREATE TRIGGER tr_TABLE_Update ON TABLE
for update
AS
begin
DECLARE
@FIELD VARCHAR(100)
,@U_FieldName varchar(100)
,@U_OldValue varchar(1000)
,@U_NewValue varchar(1000) declare cur_name1 cursor for
select U_OldValue from deleted
declare cur_name2 cursor for
select FIELD,@U_NewValue from inserted
open cur_name1
open cur_name2
fetch cur_name1 into @U_OldValue
fetch cur_name2 into @FIELD,@U_NewValue
while (@@fetch_status=0)
begin
INSERT INTO TABLE2(F1, FN, OV, NV) VALUES(@FIELD, @U_FIELDNAME, @U_OLDVALUE, @U_NEWVALUE)
fetch cur_name1 into @U_OldValue
fetch cur_name2 into @FIELD,@U_NewValue
end
close cur_name1
close cur_name2
deallocate cur_name1
deallocate cur_name2
END
for update
AS
begin
DECLARE
@FIELD VARCHAR(100)
,@U_FieldName varchar(100)
,@U_OldValue varchar(1000)
,@U_NewValue varchar(1000) declare cur_name1 cursor for
select U_OldValue from deleted (U_OldValue这个是代表你操作表的对应的字段)
declare cur_name2 cursor for
select FIELD,U_NewValue from inserted(FIELD,U_NewValue这个是代表你操作表的对应的字段)
open cur_name1
open cur_name2
fetch cur_name1 into @U_OldValue
fetch cur_name2 into @FIELD,@U_NewValue
while (@@fetch_status=0)
begin
INSERT INTO TABLE2(F1, FN, OV, NV) VALUES(@FIELD, @U_FIELDNAME, @U_OLDVALUE, @U_NEWVALUE)
fetch cur_name1 into @U_OldValue
fetch cur_name2 into @FIELD,@U_NewValue
end
close cur_name1
close cur_name2
deallocate cur_name1
deallocate cur_name2
END