insert:
CREATE TRIGGER trg_i
ON tab_a
FOR INSERT
AS
begin
insert into b select * inserted
endupdate:
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
CREATE TRIGGER trg_i
ON tab_a
FOR INSERT
AS
begin
insert into b select * inserted
endupdate:
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
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
如果一次更新多条记录呢?
运行出现
error 170 --Incorrect syntax near '*'
这是怎么回事呢?
每条一条记录的改变触发一次 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)
modify----> update b set b.f1='xx' where code=@nCOde)
END
ELSE
BEGIN
insert into b select * from inserted
END
END
END
modify----> update b set b.field1='xx' where code=@nCOde
仅仅要测试的话--> update b set code =@nCOde where code=@nCOde
error 170 --Incorrect syntax near '*'
的问题还出现?
还有其它问题?
update b set code =@nCOde where code=@nCOdeCREATE 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 code=@nCOde where code=@nCOde)
--- YOU CAN ADD YOUR code here : update b set b.field='**' where
-- code=@nCOde
END
ELSE
BEGIN
insert into b select * from inserted
END
END
END
select @nCOde=code from inserted
IF EXISTS (select * from b where b.code=@nCOde)select @nCOde=code from inserted 得不到正确的参数
当更新的列code='0001'时,测试@nCOde='0' 这是怎么回事??
也就是说 在临时表里得不到更新列的主键值??
这是怎么回事呢??
create trigger tr_1
on a
for insert,update,delete
asdelete b
from b,deleted d
where b.code=d.codeupdate b
set col1=i.col1,col2=i.col2,...colN=i.colN
from b,inserted i
where b.code=i.codeinsert b
select * from inserted i
where not exists (
select 1 from b
where b.code=i.code
)
go
b表是随a表变化的 考虑上述情况应建立级联删除触发器
但因为b表只是临时表,且a表是一个基础数据表 记录只更新或插入 不删除(否则会导致许多表数据丢失)
所以应该不会存在‘如果insert a 表,而CODE 在B表已经有了"的情况