instead of insert 用触发器来判断重复进行更新

--只管插入，用触发器来判断重复进行更新
create table v_tb(col1 INT,col2 INT)
GO

--不管你插入任何数据在v_tb中
create trigger my_tr on v_tb
instead of insert
as
begin
    update b
    set b.col2=a.col2
    from Inserted a, v_tb b
    where a.col1 = b.col1 
    
    Insert into v_tb
    select col1,col2
    from Inserted
    where NOT EXISTS (SELECT v_tb.col1
      FROM v_tb 
      WHERE v_tb.col1 = Inserted.col1)

end
go

--测试
INSERT INTO v_tb(col1,col2)
VALUES(1,3)

--测试
SELECT * FROM v_tb

 

转载于:https://www.cnblogs.com/zoumin123/p/8241422.html