SQL触发器和存储过程实例

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