写一个小触发器
A B两表结构相同 主健 code
要在A 上建触发器
insert into 或 Update A 表时
如果 insert 则 insert into b select * inserted
如果 update 则 如果 b 表有该记录 则 update b 表( b.code=inserted.code )
如果 b 表没有记录 则 insert into b select * inserted
A B两表结构相同 主健 code
要在A 上建触发器
insert into 或 Update A 表时
如果 insert 则 insert into b select * inserted
如果 update 则 如果 b 表有该记录 则 update b 表( b.code=inserted.code )
如果 b 表没有记录 则 insert into b select * inserted
insert:
CREATE TRIGGER trg_i
ON tab_a
FOR INSERT
AS
begin
insert into b select * inserted
end
update:
CREATE TRIGGER trg_i
ON tab_a
FOR UPDATE
AS
begin
delete b where b.code in (select code from inserted)
insert into b select * inserted
end <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
CREATE TRIGGER trg_i
ON tab_a
FOR INSERT
AS
begin
insert into b select * inserted
end
update:
CREATE TRIGGER trg_i
ON tab_a
FOR UPDATE
AS
begin
delete b where b.code in (select code from inserted)
insert into b select * inserted
end <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
CREATE TRIGGER trg_a
ON tab_a
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @nInsRows INT
DECLARE @nCount INT
DECLARE @nErrCod INT
DECLARE @nInt INT
DECLARE @nCOde INT
SELECT @nInsRows = COUNT(*)
FROM INSERTED
SELECT @nCount = COUNT(*)
FROM DELETED
IF (@nInsRows > 0 AND @nCount = 0) --is insert
BEGIN
insert into b select * from inserted
END
IF (@nInsRows > 0 AND @nCount > 0) -- is update
BEGIN
select @nCOde=code from inserted
IF EXISTS (select * from b where b.code=@nCOde)
BEGIN
update b set ** where code=@nCOde )
END
ELSE
BEGIN
insert into b select * from inserted
END
END
END
ON tab_a
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @nInsRows INT
DECLARE @nCount INT
DECLARE @nErrCod INT
DECLARE @nInt INT
DECLARE @nCOde INT
SELECT @nInsRows = COUNT(*)
FROM INSERTED
SELECT @nCount = COUNT(*)
FROM DELETED
IF (@nInsRows > 0 AND @nCount = 0) --is insert
BEGIN
insert into b select * from inserted
END
IF (@nInsRows > 0 AND @nCount > 0) -- is update
BEGIN
select @nCOde=code from inserted
IF EXISTS (select * from b where b.code=@nCOde)
BEGIN
update b set ** where code=@nCOde )
END
ELSE
BEGIN
insert into b select * from inserted
END
END
END
级联更新和级联删除方式:
Alter TABLE [dbo].[T_USERGROUP] ADD
CONSTRAINT [FK_T_USERGROUP_T_ACCTTEMPLATE] FOREIGN KEY
(
[ATNAME]
) REFERENCES [dbo].[T_ACCTTEMPLATE] (
[ATNAME]
) ON Update CASCADE ON Delete CASCADE
Alter TABLE [dbo].[T_USERGROUP] ADD
CONSTRAINT [FK_T_USERGROUP_T_ACCTTEMPLATE] FOREIGN KEY
(
[ATNAME]
) REFERENCES [dbo].[T_ACCTTEMPLATE] (
[ATNAME]
) ON Update CASCADE ON Delete CASCADE
转载于:https://blog.51cto.com/zanchun/262571