SQL触发器和存储过程实例-优快云博客

本文介绍了一个SQL触发器的创建及使用案例，该触发器用于处理特定格式的短信记录，并将其存储到目标表中。触发器主要关注以'PK'开头且目的地为'02'的短信消息，同时限制每个源手机号码最多发送30条这样的短信。

CREATE TRIGGER PK02 ON [dbo].[jobs] FOR INSERT AS
DECLARE @ID as int,
@SrcMobileID as nvarchar(15),
@DestMobileID as nvarchar(15),
@MsgContent as nvarchar(255),
@ArrtTime as datetime

select @ID = ID, @SrcMobileID=SrcMobileID, @DestMobileID= DestMobileID, @MsgContent= MsgContent, @ArrtTime= ArrtTime
from smsIn
where id=(select max(ID) from smsin)

---转储以PK开头发送到02的短信
if right(@DestMobileID,2)='02' and UPPER(left(@MsgContent,2))='PK' begin
select @MsgContent = right(@MsgContent,len(@MsgContent)-2)
if (select count(*) from tbl_02PK where SrcMobileID= @SrcMobileID)<30
insert into tbl_02PK (smsin_id,SrcMobileID,DestMobileID,keyword,MsgContent,ArrtTime)
values(@ID, @SrcMobileID, @DestMobileID, 'PK', @MsgContent, @ArrtTime)
end

DECLARE @ID as int,
@SrcMobileID as nvarchar(15),
@DestMobileID as nvarchar(15),
@MsgContent as nvarchar(255),
@ArrtTime as datetime
DECLARE tnames_cursor CURSOR FOR

select ID, SrcMobileID, DestMobileID, MsgContent, ArrtTime
from smsIn
where id>100000 and DestMobileID like '%02' and left(MsgContent,2)='pk'

OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @ID,@SrcMobileID,@DestMobileID,@MsgContent,@ArrtTime
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF (@@FETCH_STATUS <> -2) BEGIN
select @MsgContent = right(@MsgContent,len(@MsgContent)-2)
insert into tbl_02PK (smsin_id,SrcMobileID,DestMobileID,keyword,MsgContent,ArrtTime)
values(@ID, @SrcMobileID, @DestMobileID, 'PK', @MsgContent, @ArrtTime)
END
FETCH NEXT
FROM tnames_cursor INTO @ID,@SrcMobileID,@DestMobileID,@MsgContent,@ArrtTime
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor