复制代码 代码如下: --会员表 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_
复制代码 代码如下: --会员表 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
