我想在往表里面插入数据的时候保证删除与要插入的数据具有相同c1值的数据,用触发器怎么弄呢?CREATE TABLE t1
(
c1 INT,
c2 int
)INSERT INTO t1 VALUES(1,2)
INSERT INTO t1 VALUES(2,2)
INSERT INTO t1 VALUES(3,2)
然后我再插入一条(3,5)的数据,想触发器能帮我删除之前已经存在的这个(3,2)的数据CREATE TRIGGER checki ON t1
for INSERT
AS
IF EXISTS (SELECT c1 FROM INSERTED WHERE c1=t1.c1 )
DELETE FROM t1 WHERE c1=INSERTED.c1
go
这么写有问题啊....消息 4104,级别 16,状态 1,过程 checki,第 4 行
无法绑定由多个部分组成的标识符 "t1.c1"。
消息 4104,级别 16,状态 1,过程 checki,第 5 行
无法绑定由多个部分组成的标识符 "INSERTED.c1"。
(
c1 INT,
c2 int
)INSERT INTO t1 VALUES(1,2)
INSERT INTO t1 VALUES(2,2)
INSERT INTO t1 VALUES(3,2)
然后我再插入一条(3,5)的数据,想触发器能帮我删除之前已经存在的这个(3,2)的数据CREATE TRIGGER checki ON t1
for INSERT
AS
IF EXISTS (SELECT c1 FROM INSERTED WHERE c1=t1.c1 )
DELETE FROM t1 WHERE c1=INSERTED.c1
go
这么写有问题啊....消息 4104,级别 16,状态 1,过程 checki,第 4 行
无法绑定由多个部分组成的标识符 "t1.c1"。
消息 4104,级别 16,状态 1,过程 checki,第 5 行
无法绑定由多个部分组成的标识符 "INSERTED.c1"。
for INSERT
AS
begin
DELETE t FROM t1 t,INSERTED i WHERE t.c1=i.c1
end
CREATE TRIGGER checki ON t1
INSTEAD OF INSERT
AS
DELETE T1
FROM T1 JOIN INSERTED
ON T1.C1=INSERTED.C1INSERT T1
SELECT * FROM INSERTED
go
CREATE TRIGGER checki ON t1
for INSERT
AS
BEGIN
declare @new_c1 int, @new_c2 intselect @new_c1 = c1, @new_c2 = c2
from insertedif exists(select top 1 * from t1 where t1.c1 = @new_c1)
begin
update t1
set c2 = @new_c2
where c1 = @new_c1
end
END
go
CREATE TRIGGER checki ON t1
inserted of INSERT
AS
begin
DELETE t FROM t1 t,INSERTED i WHERE t.c1=i.c1
insert into t1 select * from INSERTED
end
(
c1 INT,
c2 int
)INSERT INTO t1 VALUES(1,2)
INSERT INTO t1 VALUES(2,2)
INSERT INTO t1 VALUES(3,2)CREATE TRIGGER tt ON t1
instead of INSERT
AS
begin
DELETE t FROM t1 t,INSERTED i WHERE t.c1=i.c1
insert into t1 select * from INSERTED
endinsert into t1 select 3,5
select * from t1
/*
c1 c2
----------- -----------
1 2
2 2
3 5(3 行受影响)
*/
drop table t1