Copy Code code as follows:
--Member Table
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
--Membership Level table
If object_id (' userlevel ', ' u ') is not null
drop table Userlevel
Go
--Inserting test data
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
--Trigger
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
--Test
Update UserInfo
Set user_tegral=100
where userid=1
SELECT * FROM UserInfo
--Another trigger
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
