原文:
存储过程，游标和触发器实例[原创]
自己写的存储过程与游标结合使用的实例，与大家分享，也供自己查阅，仅供参考：
--
使用游标循环处理，删除重复的记录
declare
@UserID
int
declare
@UserName
varchar
(
32
)
declare
@RealName
varchar
(
32
)
declare
@UnitFlag
int
declare
@Email2
varchar
(
64
)
declare
@Mobile
varchar
(
64
)
declare
@Start
int
declare
@End
int
declare
@Type
varchar
(
16
)
declare
@IsSubscribe
bit
declare
curEmailTotalLib
cursor
for
(
select
UserID,UserName,RealName,UnitFlag,Email,Mobile,IsSubscribe
from
Task_IntermediateData)
open
curEmailTotalLib
--
打开游标
fetch
next
from
curEmailTotalLib
into
@UserID
,
@UserName
,
@RealName
,
@UnitFlag
,
@Email2
,
@Mobile
,
@IsSubscribe
while
@@fetch_status
=
0
--
获取成功
begin
--
在邮件系统总库中不存在此用户ID，不存在此邮箱，并且用户订阅过
if
not
exists
(
select
*
from
Task_EmailTotalLib
where
UserID
=
@UserID
)
and
not
exists
(
select
*
from
Task_EmailTotalLib
where
Email
=
@Email2
)
and
@IsSubscribe
=
1
begin
set
@Start
=
charindex
(
'
@
'
,
@Email2
,
0
)
set
@End
=
charindex
(
'
.
'
,
@Email2
,
@Start
)
if
@Start
!=
0
and
@End
!=
0
begin
--
不是垃圾邮件
if
@Email2
is
not
null
and
ltrim
(
rtrim
(
@Email2
))
<>
''
begin
if
not
exists
(
select
*
from
Task_JunkEmail
where
Email
=
@Email2
)
begin
begin
try
set
@Type
=
substring
(
@Email2
,
@Start
+
1
,
@End
-
@Start
-
1
)
if
@Type
!=
'
qq
'
and
@Type
!=
'
126
'
and
@Type
!=
'
163
'
and
@Type
!=
'
sina
'
and
@Type
!=
'
sohu
'
and
@Type
!=
'
gmail
'
and
@Type
!=
'
hotmail
'
and
@Type
!=
'
yahoo
'
and
@Type
!=
'
139
'
and
@Type
!=
'
263
'
and
@Type
!=
'
yeah
'
and
@Type
!=
'
cnki
'
begin
set
@Type
=
'
extra
'
end
insert
into
Task_EmailTotalLib(UserID, UserName, RealName, Email, Mobile,
Priority, MailType, LibType, FpIsSend, CpIsSend, UpIsSend, VpIsSend, WpIsSend,
XpIsSend, YpIsSend, ZpIsSend, SendCount, SucCount, FailCount, CreditRate, IsJunkEmail,
IsSubscribe, IsUsed, Memo)
values
(
@UserID
,
@UserName
,
@RealName
,
@Email2
,
@Mobile
,
2
,
@Type
,
@UnitFlag
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
0
,
10
,
0
,
@IsSubscribe
,
0
,
''
)
end
try
begin
catch
print
'
@Email2:
'
+
@Email2
+
'
charindex(
''
@
''
, @Email2, 0)
'
+
charindex
(
'
@
'
,
@Email2
,
0
)
+
'
@Start
'
+
@Start
+
'
@End
'
+
@End
+
'
@End - @Start - 1:
'
+
@End
-
@Start
-
1
end
catch
end
end
end
end
fetch
next
from
curEmailTotalLib
into
@UserID
,
@UserName
,
@RealName
,
@UnitFlag
,
@Email2
,
@Mobile
,
@IsSubscribe
end
close
curEmailTotalLib
--
关闭游标
deallocate
curEmailTotalLib
--
释放游标
触发器实例：插入数据时，触发器获取这条数据ID，自动修改，比程序处理更方便。
Create trigger tg_url_update on [dbo].UrlTotal for insert as
declare @getid int;
declare @url varchar(128);
set @getid=(select id from inserted);
set @url='Test.aspx?id='+cast(@getid as varchar(50))
update UrlTotal set
url=@url
where
id=@getid
谢谢阅读~~
