MSSQL 触发器 怎么修改记录的值？
有一个表:
create table dept(
id int not null primary key,
name varchar(50),
tel varchar(50),
flag char(1),
utime datetime
)
怎么写一个触发器，当做 insert 一条记录时，utime 字段赋值 当前时间;
修改记录时，utime 字段赋值 当前时间 和 flag 赋值 'U ' ;
删除记录时，utime 字段赋值 当前时间 和 flag 赋值 'D ' 呢？
希望能给出源码，谢谢各位了!
[解决办法]
当做 insert 一条记录时，utime 字段赋值 当前时间
----------------------------------------------------
这个要求你可以在utime数据列上创建默认值default为getdate()
[解决办法]
----创建测试数据
if object_id( 'tbdept ') is not null
drop table tbdept
GO
create table dept(
id int not null primary key,
name varchar(50),
tel varchar(50),
flag char(1),
utime datetime
)
GO
----创建INSERT触发器
create trigger trg_insert_dept on tbdept
for insert
as
update a set utime = getdate() from tbdept as a inner join inserted as i
on a.id = i.id
GO
----创建UPDATE触发器
create trigger trg_update_dept on tbdept
for update
as
update a set utime = getdate(),flag = 'U ' from tbdept as a inner join inserted as i
on a.id = i.id
GO
[解决办法]
抱歉,更正一下,上面回复中的create table dept应为create table tbdept:
----创建测试数据
if object_id( 'tbdept ') is not null
drop table tbdept
GO
create table tbdept(
id int not null primary key,
name varchar(50),
tel varchar(50),
flag char(1),
utime datetime
)
GO
----创建INSERT触发器
create trigger trg_insert_dept on tbdept
for insert
as
update a set utime = getdate() from tbdept as a inner join inserted as i
on a.id = i.id
GO
----创建UPDATE触发器
create trigger trg_update_dept on tbdept
for update
as
update a set utime = getdate(),flag = 'U ' from tbdept as a inner join inserted as i
on a.id = i.id
GO
[解决办法]
create trigger tri_A on dept instead of insert as
insert dept(name,tel,flag,utime) select name,tel,flag,getdate() from inserted
create trigger tri_A on dept instead of update as
update dept set name=b.name,tel=b.tel,flag= 'D ',utime=getdate() from inserted
--删除了还怎么赋值
[解决办法]
create trigger tri_A on dept instead of update as
update dept set name=b.name,tel=b.tel,flag= 'U ',utime=getdate() from inserted b
where id=b.id
[解决办法]
create trigger iuddept on dept
for insert,update ,delete
as
begin
if not exists(select 1 from deleted)
begin
update dept set utime=getdate() from dept a,inserted b
where a.id=b.id
end
if not exists(select 1 from inserted)
begin
insert into dept select id,name,tel, 'D ',getdate() from deleted
end
if exists(select 1 from inserted) and exists(select 1 from deleted)
begin
update a set utime=getdate(),flag= 'U ' from dept a,deleted b,inserted i
where a.id=b.id and i.id=a.id
end
end
------解决方案--------------------
测试已经通过！！！
