复制代码 代码如下:

--会员表

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

go

--会员等级表

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

drop table userlevel

go

--插入测试数据

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

go

--触发器

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

go

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