34,575
社区成员
发帖
与我相关
我的任务
分享
CREATE TRIGGER TRI_TEST ON A
AFTER UPDATE
AS
BEGIN
IF UPDATE(C) AND EXISTS(SELECT 1 FROM inserted WHERE datediff(nn,d,c)>0)
BEGIN
INSERT INTO B(a,b,c)
SELECT a,b,datediff(nn,d,c) FROM inserted
END
END;
if object_id('A') is not null
drop table A;
go
create table A (a int primary key, b int, c date, d date);
go
insert into A values(1,10,'2010-10-01','2010-10-02');
go
if object_id('B') is not null
drop table B;
go
create table B (a int, b int, c int);
go
create trigger trg_update_A on A
for update
as
if update(c)
begin
insert into B
select i.a,i.b,datediff(dd,d.d,i.c) d
from inserted i join deleted d on i.a=d.a where i.c>d.d;
end
go
update A set c='2010-10-10' where a=1;
select * from a;
/*1 10 2010-10-10 2010-10-02*/
select * from b;
/*1 10 8*/
CREATE TABLE B
(a int NULL, b int NULL, c DATETIME)
CREATE TABLE A
(a int NULL, b int NULL,c DATETIME ,d DATETIME)
GO
CREATE TRIGGER A_trig
ON A
FOR INSERT
AS
IF UPDATE(c)
INSERT INTO B
SELECT a,b,c FROM A
PRINT 'A表数据已插入B表'
GO
INSERT INTO A
SELECT 1,2,2010-9-10,NULL
SELECT * FROM A
SELECT * FROM B
---只会写简单点的,楼下的继续 UPUP!