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
------解决方案--------------------