第一个用存储过程插入比较好,比如TB表有COL1,COL2,以COL1为判定条件 CREATE PROCEDURE INS_OR_UPD_TB(@COL1 INT,@COL2 INT) AS BEGIN IF EXISTS(SELECT 1 FROM TB WHERE COL1=@COL1) UPDATE TB SET COL2=@COL2 WHERE COL1=@COL1 ELSE INSERT INTO TB(COL1,COL2) SELECT @COL1,@COL2 END第二个可以用触发器实现,还是用上面的表作例子 CREATE TRIGGER TRI_TB ON TB AFTER INSERT,UPDATE AS BEGIN IF EXISTS(SELECT 1 FROM TB T1 WHERE EXISTS(SELECT 1 FROM TB T2 WHERE T2.COL1=T1.COL1 AND T1.COL2=T2.COL2)) ROLLBACK TRAN PRINT '发现重复记录,事务已回滚' END
这里你参考一下
CREATE PROCEDURE INS_OR_UPD_TB(@COL1 INT,@COL2 INT)
AS
BEGIN
IF EXISTS(SELECT 1 FROM TB WHERE COL1=@COL1)
UPDATE TB SET COL2=@COL2 WHERE COL1=@COL1
ELSE
INSERT INTO TB(COL1,COL2) SELECT @COL1,@COL2
END第二个可以用触发器实现,还是用上面的表作例子
CREATE TRIGGER TRI_TB ON TB
AFTER INSERT,UPDATE
AS
BEGIN
IF EXISTS(SELECT 1 FROM TB T1 WHERE EXISTS(SELECT 1 FROM TB T2 WHERE T2.COL1=T1.COL1 AND T1.COL2=T2.COL2))
ROLLBACK TRAN
PRINT '发现重复记录,事务已回滚'
END