建立SqlServer資料庫觸發器腳本 範例

--會員表

if object_id('userinfo','u') is not null

drop table userinfo

go

create table userinfo(userid int primary key,user_tegral int,level int)

insert into userinfo select 1,0,0

--會員等級表

if object_id('userlevel','u') is not null

drop table userlevel

--插入測試資料

create table userlevel(level int primary key,mlevel_point decimal(10,2))

insert into userlevel select 0,0

insert into userlevel select 1,100

insert into userlevel select 2,200

insert into userlevel select 3,300

--觸發器

create trigger TR_userInfor

on userinfo for update

as

begin

update a

set a.level=b.level

from userinfo a,userlevel b

where a.userid in(Select userid from inserted) and a.user_tegral>=b.mlevel_point and

a.user_tegral<(Select min(mlevel_point)

from userlevel where mlevel_point>b.mlevel_point)

end

--測試

update userinfo

set user_tegral=100

where userid=1

select * from userinfo

--另外一個觸發器

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER TRIGGER [AlterName]

ON [dbo].[FS_User]

FOR INSERT

AS

BEGIN

SET NOCOUNT ON;

UPDATE dbo.FS_User SET uname=(SELECT uname FROM Inserted)+'@ml'

WHERE id IN (SELECT id FROM inserted)

END