SQL Server 2005系列教學_ 觸發器

常見的觸發器型別:
插入觸發器  insert
刪除觸發器  delete
更新觸發器  update

插入觸發器:

語法:

create trigger 名稱
on 表名  for insert
as
begin
   程式碼
end

select  * from users

insert into users values ('張三','男',20,40)

例：禁止插入新記錄

alter trigger inse01
on users for insert
as
begin
  if exists( select  * from  inserted)   或者寫成 count(*)
    rollback
   print '禁止插入新員工資訊'
end

insert into users values ('張三風','男',20,40)
select  * from users

--simpletrigger.sql
--簡單的觸發器
use northwind

例：
alter trigger 簡單觸發器 on dufei for insert as
declare @ID int,@username nvarchar(50)
set @ID = @@identity
set @username = (select username from dufei where ID=@ID)
print '最後寫入的記錄的編號是:' + convert(nvarchar,@ID) + '  使用者名稱是:' + @username
go
insert dufei (username) values('吳小華')

但如果在print 後加一個rollback結果就不同了。說明是手插入到表中了，但又回滾了。

select @@identity
create table dufei
(id  int identity(1,1),
username varchar(10))

--drop trigger reminder

insert into employees(LastName,FirstName)
values('hahaha1','xixixi1')

select * from employees

--禁止觸發器
alter table employees disable trigger reminder
 
--啟用觸發器
alter table employees enable trigger reminder
select * from employees

解除觸發器:(刪除觸發器)
drop trigger 名稱
drop trigger inse01

禁止插入行政部新員工

alter trigger inse02
on yuangong for insert
as
begin
if exists(select * from inserted where 部門='行政部' )
begin
  rollback
  print  '你的許可權不夠,不能插入行政部新員工!'
end
end

select  * from yuangong
delete from yuangong  where 姓名='於洋'

insert into yuangong values  ('0001','於洋','男',20,'2007-12-26','河南',3000,2000,500,1,'123456','保安部')

count(*)

create trigger inse03
on yuangong for insert
as
begin
     if (select count(*) from inserted  where 部門='' )>0
     begin
          rollback
          print  '不能插入新記錄'
     end
end

drop trigger inse03

insert into yuangong values  ('0001','於洋','男',20,'2007-12-26','河南',3000,2000,500,1,'123456','保安部')

刪除觸發器:
 create trigger 名稱
 on 表名 for delete
 as
 begin
   程式碼
  end

用到一個臨時表: deleted 存放的是準備刪除的記錄

禁止刪除技術部的員工

  alter trigger del01
  on  yuangong for delete
  as
  begin
     if exists(select * from deleted  where 部門='人事部')
     begin
         rollback
         print  '不能刪除人事部員工'
     end
  end

select * from yuangong
delete from yuangong where 編號='0006'

更新觸發器:
用到臨時表: 兩個:
inserted
deleted

禁止使用者更新:
create  trigger upd01
on yuangong for update
as
begin
    if exists(select  * from inserted)
    begin
        rollback
        print  '不允許更改資料!'
    end
end

禁止更新基本工資和獎金

create  trigger upd01
on yuangong for update
as
begin
    if update(基本工資) or update(獎金)
    begin
        rollback
        print  '不允許更改資料!'
    end
end

 

select  * from yuangong

update yuangong set 年齡=30  where 姓名='於洋'
Create unique ix_name   on hr (姓，名)
create [unique][clustered][nonstered] index on 資料表名稱(列名稱)
只允許更改姓名,性別,年齡

create trigger upda02
on yuangong for update
as
begin
   if update(姓名) or update(性別)  or update (年齡)
    begin
        commit
     end
    else
    begin
      rollback
      print  '不能修改姓名,性別,年齡之外的資料'
     end
end

測試:

select * from yuangong
update yuangong set  姓名='於小洋',性別 ='女',年齡=30   where 姓名='於洋'

update yuangong set 編號='0008' where 姓名='於小洋'

create  table 學生表
(
姓名 nvarchar(8) not null primary key,
性別 nvarchar(2) not null ,
年齡 tinyint
)
create table 人數表
(
性別 nvarchar(2),
人數 int
)
insert 人數表 values ('女',0)
insert 人數表 values ('男',0)

drop TABLE 學生表

select  * from 學生表
select  * from 人數表

create trigger test2
on 學生表 for insert
as
begin
   if exists (select * from inserted where 性別='男')
   begin
     update 人數表 set 人數=(select count(*) from 學生表 group by 性別 having 性別='男')
     where 性別='男'
   end
   else
   begin
     if exists (select * from inserted where 性別='女')
      begin
         update 人數表 set 人數=(select count(*) from 學生表 group by 性別 having 性別='女')
          where 性別='女'
       end
   end
end

select  * from 學生表
select  * from 人數表

insert into 學生表 values ('小王','男',20)
insert into 學生表 values ('小胡','男',19)

alter trigger upd001
on 學生表  for  insert
as
begin
 
 update 人數表  set  人數=人數+1  where 性別 in (select 性別 from inserted )
end

drop trigger test2

select  * from 學生表
select  * from 人數表
insert into 學生表 values ('小孫','女',20)
insert into 學生表 values ('小紀','女',19)
delete from  學生表  where 性別='女'

實現刪除學生記錄

create trigger dele01
on 學生表 for delete
as
if exists(select  * from deleted)
 begin
    update 人數表 set 人數=(select count(*) from 學生表 where 性別='男')  where 性別='男'
        update 人數表 set 人數=(select count(*) from 學生表 where 性別='女')  where 性別='女'
 end
測試：  分開刪除