SQL 触发器

  create trigger trigger_name
           on {table_name | view_name}
           {for | After | Instead of }
           [ insert, update,delete ]
           as
           sql_statement

create trigger orderinsert
on orders
after insert
as
if (select status from goods,inserted
where goods.name=inserted.goodsname)=1
begin
print 'the goods is being processed'
print 'the order cannot be committed'
rollback transaction   --回滚﹐避免加入
end

create trigger orderinsert1
on orders
after insert
as
update goods set storage=storage-inserted.quantity
from goods,inserted
where
goods.name=inserted.goodsname

create trigger goodsdelete
on goods
after delete
as
delete from orders
where goodsname in
(select name from deleted)

create trigger orderdateupdate
on orders
after update
as
if update(orderdate)
begin
raiserror(' orderdate cannot be modified',10,1)
rollback transaction
end

create trigger orderinsert3
on orders
after insert
as
if (select count(*) from goods,inserted where goods.name=inserted.goodsname)=0
begin
print ' no entry in goods for this order'
rollback transaction
end

CREATE TABLE [dbo].[Teacher](

    [eno] [char](10) COLLATE Chinese_PRC_CI_AS NULL,

    [name] [char](6) COLLATE Chinese_PRC_CI_AS NULL,

    [sal] [int] NULL,

    [pjob] [char](6) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[sal_log](

    [eno] [char](10) COLLATE Chinese_PRC_CI_AS NULL,

    [sal] [int] NULL,

    [username] [char](10) COLLATE Chinese_PRC_CI_AS NULL,

    [date] [datetime] NULL

) ON [PRIMARY]

GO

Create Trigger trig_Insert_Teacher

On Teacher

For Insert

as

Declare    @pjob char(4),@sal numeric,@eno char(40)

Select @pjob=pjob,@sal = sal,@eno = eno From Inserted

If(@pjob='教授') and (@sal < 4000)

Begin

    Update Teacher Set Sal = 4000 Where Eno = @Eno

End

go

Drop Trigger trig_insert_teacher_1

go

Create Trigger trig_insert_teacher_1

On Teacher For Insert

As

Declare @eno char(40),@sal numeric

Select @eno=eno,@sal=sal From Inserted

Insert Into sal_log Values(@eno,@sal,current_user,current_timestamp)

go

Drop Trigger trig_update_teacher

Go

Create Trigger trig_update_teacher

On Teacher For Update

As

Declare @eno char(10),@sal1 int,@sal2 int

Select @eno=eno,@sal1=sal From Inserted

Select @sal2=sal From Deleted

if(@sal1<>@sal2)

Begin

    Insert Into sal_log Values(@eno,@sal1,current_user,current_timestamp)

End

Go