小小触发不足为患

CREATE TRIGGER SYNC4J_USER_DELETE ON [SYNC4J_USER]
2

FOR DELETE
3

AS
4

begin
6

delete SYNC4J_PRINCIPAL from SYNC4J_PRINCIPAL a,deleted d where a.username=d.username
7

end
8

CREATE TRIGGER SYNC4J_USER_INSERT ON [SYNC4J_USER]
2

FOR INSERT
3

AS
4

DECLARE @v_id int
6

DECLARE @N_USERID int
7

DECLARE @N_NAME varchar
8

begin
10

select top 1 @N_USERID=a.userid,@N_NAME=a.username from SYNC4J_USER as a INNER JOIN Inserted AS i ON a.userid=i.userid
11

select @v_id=max(groupid)+1 from txl_groupinfo
12

if @v_id is null
13

begin
14

set @v_id=10001
15

end
16

--默认初始化用户组
17

insert into txl_groupinfo (GROUPID,USERID,GROUPNAME,STATUS,CREATETIME) values (@v_id,@N_USERID,'家人',0,getdate())
18

insert into txl_groupinfo (GROUPID,USERID,GROUPNAME,STATUS,CREATETIME) values (@v_id+1,@N_USERID,'朋友',0,getdate())
19

insert into txl_groupinfo (GROUPID,USERID,GROUPNAME,STATUS,CREATETIME) values (@v_id+2,@N_USERID,'同学',0,getdate())
20

insert into txl_groupinfo (GROUPID,USERID,GROUPNAME,STATUS,CREATETIME) values (@v_id+3,@N_USERID,'同事',0,getdate())
21

--初始化注册用户可免费发送短信条数：20
22

insert into txl_limited (userid,smsinitnum,smsusednum,smsleftnum,emailinit,emailusednum,emailleftnum) values (@N_USERID,20,0,20,3,0,3)
23

select @v_id=max(id)+1 from SYNC4J_PRINCIPAL
25

if @v_id is null
26

begin
27

set @v_id=1
28

end
29

insert into SYNC4J_PRINCIPAL (username,device,id) values (@N_NAME,'syncml-phone',@v_id)
31

--初始化用户积分
32

insert into admin_member_mark (userid,mark,coin) values (@N_USERID,0,0)
34

end
35