USE tempdb GO IF OBJECT_ID('trig_A_I_U') IS NOT NULL DROP TRIGGER trig_A_I_U IF OBJECT_ID('A') IS NOT NULL DROP TABLE A IF OBJECT_ID('B') IS NOT NULL DROP TABLE B GO CREATE TABLE A( id INT, [name] NVARCHAR(20), [type] INT, applyType INT ) CREATE TABLE B( id INT, [name] NVARCHAR(20), [type] INT, applyType INT ) GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE TRIGGER dbo.trig_A_I_U ON dbo.A AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON; IF EXISTS(SELECT * FROM INSERTED WHERE type=2 AND applyType=11) --AND NOT EXISTS (SELECT * FROM DELETED) --如果不需要更新时插入到B, 去掉注释即可 BEGIN INSERT INTO B(id,[name],[type],applyType) SELECT id,[name],[type],applyType FROM INSERTED WHERE TYPE=2 AND applyType=11 END END GOINSERT INTO A VALUES (1,'a',2,11) INSERT INTO A VALUES (2,'b',3,11) INSERT INTO A VALUES (3,'b',4,11)UPDATE A SET [type]=2 WHERE id=3SELECT * FROM A SELECT * FROM B
INSERT INTO B(id,[name],[type],applyType,value) SELECT id,[name],[type],applyType,'Y' FROM INSERTED WHERE TYPE=2 AND applyType=11
你好 这个触发器 如果想对已经UPDATE A SET [type]=2 WHERE id=3一遍之后 再update一遍那b表又会出现一条重复的数据 为防止这种误操作 要怎么改呢?
你好 这个触发器 如果想对已经UPDATE A SET [type]=2 WHERE id=3一遍之后 再update一遍那b表又会出现一条重复的数据 为防止这种误操作 要怎么改呢? 插入时做个判断 INSERT INTO B(id,[name],[type],applyType) SELECT id,[name],[type],applyType FROM INSERTED WHERE TYPE=2 AND applyType=11 and not exists(select 1 from B where b.id=INSERTED .id) 如果id是主键的话 如果你判断重复是多个字段一起判断的话,and not exists(select 1 from B where b.id=INSERTED .id)在这段括号里加上其他字段的判断
GO
IF OBJECT_ID('trig_A_I_U') IS NOT NULL DROP TRIGGER trig_A_I_U
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
GO
CREATE TABLE A(
id INT,
[name] NVARCHAR(20),
[type] INT,
applyType INT
)
CREATE TABLE B(
id INT,
[name] NVARCHAR(20),
[type] INT,
applyType INT
)
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER dbo.trig_A_I_U
ON dbo.A
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM INSERTED WHERE type=2 AND applyType=11)
--AND NOT EXISTS (SELECT * FROM DELETED) --如果不需要更新时插入到B, 去掉注释即可
BEGIN
INSERT INTO B(id,[name],[type],applyType)
SELECT id,[name],[type],applyType FROM INSERTED WHERE TYPE=2 AND applyType=11
END
END
GOINSERT INTO A VALUES (1,'a',2,11)
INSERT INTO A VALUES (2,'b',3,11)
INSERT INTO A VALUES (3,'b',4,11)UPDATE A SET [type]=2 WHERE id=3SELECT * FROM A
SELECT * FROM B
还有最后一个问题,请问如果现在A表,多了一个字段value,B表的这个value字段的值需要设定成固定的,比如值是"Y"。前面的条件不变,还是满足type为2且applytypoe=11。请问这个如何写,跪谢
那你就insert into select时,select中把这个字段值固定成Y就可以了。
SELECT id,[name],[type],applyType,'Y' FROM INSERTED WHERE TYPE=2 AND applyType=11
你好 这个触发器 如果想对已经UPDATE A SET [type]=2 WHERE id=3一遍之后 再update一遍那b表又会出现一条重复的数据 为防止这种误操作 要怎么改呢?
你好 这个触发器 如果想对已经UPDATE A SET [type]=2 WHERE id=3一遍之后 再update一遍那b表又会出现一条重复的数据 为防止这种误操作 要怎么改呢?
插入时做个判断
INSERT INTO B(id,[name],[type],applyType)
SELECT id,[name],[type],applyType FROM INSERTED WHERE TYPE=2 AND applyType=11
and not exists(select 1 from B where b.id=INSERTED .id)
如果id是主键的话
如果你判断重复是多个字段一起判断的话,and not exists(select 1 from B where b.id=INSERTED .id)在这段括号里加上其他字段的判断