SET @A=(SELECT A FROM A) 这个不行的 因为你后面查询可能返回多个值。不能赋值给变量。要用SELECT。这样会把最后一个值赋值给变量。 你这样还是有问题。 insert into DBA.DBO.A select * from inserted where c=1
CREATE TRIGGER [dbo].[INSERT_IN_DBA] ON [dbo].[A] FOR INSERT AS DECLARE @A INT DECLARE @B INT DECLARE @C INT IF UPDATE(C) BEGIN SET @A=(SELECT A FROM A) SET @B=(SELECT B FROM A) SET @C=(SELECT C FROM A) INSERT INTO DBA.DBO.A(A,B,C) SELECT * FROM DBB.DBO.A WHERE C=1 DELETE FROM A WHERE C=1 END你参考一下
啥意思,就是UPDATE表不触发。INSERT 才触发吗?
谢谢,我按照你的方法改实现了,我还有个问题,就是当程序处理完的时候,把C列的值更改为1触发器没被触发,如果有新数据传入到表中触发器才被触发,这是怎么回事insert into DBA.DBO.A select * from deletedwhere c=1 更新触发的话,这样修改下
谢谢,我按照你的方法改实现了,我还有个问题,就是当程序处理完的时候,把C列的值更改为1触发器没被触发,如果有新数据传入到表中触发器才被触发,这是怎么回事 select * from inserted 改为 select * from deleted试下
谢谢,我按照你的方法改实现了,我还有个问题,就是当程序处理完的时候,把C列的值更改为1触发器没被触发,如果有新数据传入到表中触发器才被触发,这是怎么回事insert into DBA.DBO.A select * from deletedwhere c=1 更新触发的话,这样修改下这个不对吧。你这个 是把C的值由1改成其他的时候触发。
谢谢,我按照你的方法改实现了,我还有个问题,就是当程序处理完的时候,把C列的值更改为1触发器没被触发,如果有新数据传入到表中触发器才被触发,这是怎么回事 select * from inserted 改为 select * from deleted试下 额,还是不行额,只有insert的触发 ALTER TRIGGER [dbo].[INSERT_IN_DBA] ON [dbo].[A] FOR INSERTAS IF UPDATE(C) BEGIN INSERT INTO DBA.DBO.A(A,B,C) SELECT * FROM deleted WHERE C=1 DELETE FROM A WHERE C=1 END 是不是FOR INSERT AS这个原因,我改成为FOR UPDATE AS之后,不能更改源数据了,会提示已更新或删除的行值不能使改行成为唯一行
FOR 后面加了UPDATE之后提示 已更新或删除的行值不能使改行成为唯一行
create table a (a int,b int, c int ) insert into a values(1,1,0),(2,2,0) create table b (a int, b int,c int) go create TRIGGER [INSERT_IN_DBA] ON [dbo].[A] FOR INSERT,update AS IF UPDATE(C) BEGIN INSERT INTO b(A,B,C) SELECT * FROM inserted WHERE C=1 DELETE FROM A WHERE C=1 END update a set c=1 where a=1--结果 a b c ----------- ----------- ----------- 2 2 0(1 行受影响)a b c ----------- ----------- ----------- 1 1 1(1 行受影响)我测试的没问题啊。实现了插入到表B 。删除表A的记录啊。
FOR 后面加了UPDATE之后提示 已更新或删除的行值不能使改行成为唯一行
你试下 CREATE TRIGGER [dbo].[INSERT_IN_DBA] ON [dbo].[A] FOR INSERT,UPDATE AS IF UPDATE(C) BEGIN INSERT INTO A2 SELECT * FROM A WHERE C=1 DELETE FROM A WHERE C=1 END GO
SET @A=(SELECT A FROM A) -- 改成 -- 如果插入一行数据 select @A = A from inserted -- 如果插入多行数据,要 用个cursor 处理一下。
--inserted的逻辑表,应该可以解决你的问题,参考下: alter TRIGGER [dbo].[INSERT_IN_DBA] ON [dbo].[A] FOR update AS IF UPDATE(C) BEGIN INSERT INTO DBA.DBO.A select * from inserted DELETE FROM A WHERE C=1 END
忘记对应上你的表了 CREATE TRIGGER [dbo].[INSERT_IN_DBA] ON [dbo].[A] FOR INSERT,UPDATE AS IF UPDATE(C) BEGIN INSERT INTO [DBA].[dbo].[A] SELECT * FROM A WHERE C=1 DELETE FROM A WHERE C=1 END GO
因为你后面查询可能返回多个值。不能赋值给变量。要用SELECT。这样会把最后一个值赋值给变量。
你这样还是有问题。
insert into DBA.DBO.A
select * from inserted
where c=1
ON [dbo].[A]
FOR INSERT AS
DECLARE @A INT
DECLARE @B INT
DECLARE @C INT
IF UPDATE(C)
BEGIN
SET @A=(SELECT A FROM A)
SET @B=(SELECT B FROM A)
SET @C=(SELECT C FROM A)
INSERT INTO DBA.DBO.A(A,B,C)
SELECT * FROM DBB.DBO.A WHERE C=1
DELETE FROM A WHERE C=1
END你参考一下
select * from deletedwhere c=1
更新触发的话,这样修改下
select * from inserted
改为
select * from deleted试下
select * from deletedwhere c=1
更新触发的话,这样修改下这个不对吧。你这个 是把C的值由1改成其他的时候触发。
select * from inserted
改为
select * from deleted试下
额,还是不行额,只有insert的触发
ALTER TRIGGER [dbo].[INSERT_IN_DBA]
ON [dbo].[A]
FOR INSERTAS
IF UPDATE(C)
BEGIN
INSERT INTO DBA.DBO.A(A,B,C)
SELECT * FROM deleted WHERE C=1
DELETE FROM A WHERE C=1
END
是不是FOR INSERT AS这个原因,我改成为FOR UPDATE AS之后,不能更改源数据了,会提示已更新或删除的行值不能使改行成为唯一行
已更新或删除的行值不能使改行成为唯一行
create table a (a int,b int, c int )
insert into a values(1,1,0),(2,2,0)
create table b (a int, b int,c int)
go
create TRIGGER [INSERT_IN_DBA]
ON [dbo].[A]
FOR INSERT,update AS
IF UPDATE(C)
BEGIN
INSERT INTO b(A,B,C)
SELECT * FROM inserted WHERE C=1
DELETE FROM A WHERE C=1
END
update a set c=1 where a=1--结果
a b c
----------- ----------- -----------
2 2 0(1 行受影响)a b c
----------- ----------- -----------
1 1 1(1 行受影响)我测试的没问题啊。实现了插入到表B 。删除表A的记录啊。
已更新或删除的行值不能使改行成为唯一行
你试下
CREATE TRIGGER [dbo].[INSERT_IN_DBA]
ON [dbo].[A]
FOR INSERT,UPDATE AS
IF UPDATE(C)
BEGIN
INSERT INTO A2
SELECT * FROM A WHERE C=1
DELETE FROM A WHERE C=1
END
GO
SET @A=(SELECT A FROM A)
-- 改成
-- 如果插入一行数据
select @A = A from inserted
-- 如果插入多行数据,要 用个cursor 处理一下。
--inserted的逻辑表,应该可以解决你的问题,参考下:
alter TRIGGER [dbo].[INSERT_IN_DBA]
ON [dbo].[A]
FOR update
AS
IF UPDATE(C)
BEGIN
INSERT INTO DBA.DBO.A select * from inserted
DELETE FROM A WHERE C=1
END
CREATE TRIGGER [dbo].[INSERT_IN_DBA]
ON [dbo].[A]
FOR INSERT,UPDATE AS
IF UPDATE(C)
BEGIN
INSERT INTO [DBA].[dbo].[A]
SELECT * FROM A WHERE C=1
DELETE FROM A WHERE C=1
END
GO