SQL-trigger觸發程序使用方法－百花

創建出來的trigger會放在資料表-觸發程序

用意 :在commit之前做的,只要不符合裡面的規則,就會被擋下來

語法：

create trigger 新建名稱 on 要執行的資料表

after 在做了什麼動作後執行下面事件(insert,update,delete)

begin

這裡放事件

end

PS:

　1　一張資料表裡面只能有一個instead　參考範例六

　2　假如裡面一張資料裡有許多觸發程序 ,每當你輸入一個動作,電腦會自動跑所有的觸發程序

以下是範例
create trigger show新增課程 on 課程 --在課程資料表裡面新增一個觸發程序
after insert --在做了insert這個動作後觸發下面事件
as
begin
select * from inserted --取得即將要新增的資料
end
go
--------
insert into 課程 values ('cs129','sdfdsg',1) ---觸發上面的事件
go

---------觸發程序範例二
update 客戶 set 姓名='江大魚',電話='07-7777777'
output inserted.姓名 as new_name,inserted.電話 as new_tel,deleted.姓名,deleted.電話
where 客戶編號='c011'
go

create trigger shoe客戶更新資料 on 客戶
after update
as
begin
select inserted.姓名 as new_name,inserted.電話 as new_tel from inserted
select deleted.姓名 as old_name,deleted.電話 as old_name from deleted --取出刪除資料
end

update 客戶 set 姓名='小魚',電話='08-888888' --當更新c011的資料的時候,會show出更新前後的資料
where 客戶編號='c011'

------------------範例三
---只要不滿足以下事件,就會擋下
--假設一學期一個人只能選四門課
create trigger 選課數檢查 on 班級
after insert
as
begin
declare @num int
select @num=count(*) from 班級 where 學號=(select 學號 from inserted) --統計她選了多少門課從inserted佔存資料裡抓出學生

   if(@num>=5)   --show出資訊必須用>=5 用=5會出現BUG
   begin
       print '已經選滿5門課,請先退選'
       rollback --把資料回復上一個動作,沒輸入此行只會show出資訊,但還是會新增進去
   end

end

insert into 班級 values('I001','S001','CS111','2000-1-1','180-M') --測試

------------範例四

create trigger 學分數檢查 on 課程
after update
as
begin
   declare @old int,@new int
   select @old=學分 from deleted
   select @new=學分 from inserted

   if @new<@old
   begin
       print '學分數不可比原來低! 原學分數為'+cast(@old as char)
       rollback
   end
end

update 課程 set 學分=2 where 課程編號='CS101' --測試

----------------------範例五
create trigger 檢查員工 on 員工
after update,delete --指定在update跟delete兩個動作
as
begin
   if exists(select * from 學生 where 姓名=(select 姓名 from deleted))
   begin
       rollback
   end
end

update 員工 set 薪水=薪水*1.03 where 姓名='張無忌' --測試

------------範例六

------------把原來的操作取代掉,假如課程裡有重複的資料會用update,假如沒有,用insert

create trigger 新增課程 on 課程
instead of insert
as
begin

   if exists(select * from 課程 where 課程編號=(select 課程編號 from inserted))
   begin
       --如果課程編號存在執行update,不存在執行insert
       update 課程 set 名稱=inserted.名稱,學分=inserted.學分
       from 課程 inner join inserted on 課程.課程編號=inserted.課程編號
   end

   else
   begin
       insert into 課程 select * from inserted
   end

end

insert into 課程 values('CS9','SDG',3) --測試

--資料庫觸發程序DDL Trigger
--不可使用 instead
--為了防止有時突然想要變更,可以在可程式性-資料庫觸發程序那按右鍵選停用,之後在開啟
create trigger readonly_table on database --不必特定指定資料庫
for drop_table,alter_table --不給人執行drop跟alter,只給人read
as
begin
rollback
end

drop table 課程2 ---測試