Sql Server触发器

USE [SY_ZD_HL]
GO
/****** Object:  Trigger [dbo].[Tri_T_SendInformation]    Script Date: 06/20/2013 14:33:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Tri_T_SendInformation] ON [dbo].[T_SendInformation]
FOR INSERT
AS

Declare @up_Down_Well_Sign int;
Declare @sms_Com_Code int;
Declare @personCard as varchar(10);
Declare @Sms_Number1 as varchar(11);
Declare @Sms_Number2 as varchar(11);
Declare @Worker_Name as varchar(10);
Declare @rj_info as int;
Declare @rj_info_sex as int;
Declare @cj_info as int;
declare @sendNos as int;
Declare @in_day_Sms as int;
Declare @out_day_Sms as int;
        /**print @up_Down_Well_Sign;获取插入入井或出井标示：1为入井，2为出井**/
        set @up_Down_Well_Sign=(SELECT upDownWellSign FROM inserted);
       
        /**print @sendNos; 短信发送的主键**/
        set @sendNos=(SELECT sendNo FROM inserted);
       
        set @personCard=(SELECT personCard FROM inserted);
        print '定位卡号：'+@personCard;
       
        /**获取随机的端口开启号码**/
        set @sms_Com_Code=(SELECT top 1 COM_CODE FROM SMS_PORT_NUMBER where COM_IS='1' ORDER BY NEWID());
        print '端口号：';
        print @sms_Com_Code;
       
        /**获取亲情号码1**/
        set @Sms_Number1 = (select a.SMS_NUMBER1 from JC_WORKER_INFO a where a.Worker_id=(select top 1 b.WORKER_ID from jc_worker_info_card b where b.CARD_NUM=@personCard))
        print '亲情号码1：';
        print '亲情号码1：'+@Sms_Number1;
       
        /**获取亲情号码2**/
        set @Sms_Number2 = (select a.SMS_NUMBER2 from JC_WORKER_INFO a where a.Worker_id=(select top 1 b.WORKER_ID from jc_worker_info_card b where b.CARD_NUM=@personCard))
        print '亲情号码2：'+@Sms_Number2;
       
        /**获取当事人名称**/
        set @Worker_Name = (select a.WORKER_NAME from JC_WORKER_INFO a where a.Worker_id=(select top 1 b.WORKER_ID from jc_worker_info_card b where b.CARD_NUM=@personCard))
        print '当事人名称：'+@Worker_Name;
       
        /**5小时内是否有入井信息并且已发短信**/
        /**set @rj_info = (select(select count(*) from T_SendInformation a where CONVERT(datetime,SignInTime,120)<=getdate() and CONVERT(datetime,SignInTime,120)>dateadd(HOUR,-5,getdate()) and upDownWellSign=1 and sendSign='1' and a.personCard=personCard) FROM inserted)**/
        set @rj_info = (select count(*) from T_SendInformation a where CONVERT(datetime,a.SignInTime,120)<=getdate() and CONVERT(datetime,a.SignInTime,120)>dateadd(HOUR,-5,getdate()) and a.upDownWellSign=1 and a.sendSign='1' and a.personCard=@personCard)
        print '五小时内成功发送短信条数：';
        print @rj_info;
       
       
        /**4小时内是否有有效入井信息**/
        set @rj_info_sex = (select count(*) from T_SendInformation a where CONVERT(datetime,SignInTime,120)<=getdate() and CONVERT(datetime,SignInTime,120)>dateadd(HOUR,-4,getdate()) and upDownWellSign=1 and sendSign='1' and a.personCard=@personCard)
        print '四小时内有效入井短信条数：';
        print @rj_info_sex;
       
       
        /**4小时内是否有出井信息**/
        set @cj_info = (select count(*) from T_SendInformation a where CONVERT(datetime,SignInTime,120)<=getdate() and CONVERT(datetime,SignInTime,120)>dateadd(HOUR,-4,getdate()) and upDownWellSign=2 and sendSign='1' and a.personCard=@personCard)
        print '四小时内有效出井信息条数：';
        print @cj_info;
       
        /**当天从0点到24点成功入井短信**/
        set @in_day_Sms = (select count(*) from T_SendInformation where CONVERT(datetime,SignInTime,23)>=CONVERT(date,getdate(),111)  and  CONVERT(datetime,SignInTime,23)<CONVERT(date,dateadd(day,1,getdate()),111) and upDownWellSign=1 and sendSign='1' and personCard=@personCard);
        print '0点到24点成功入井短信条数：';
        print @in_day_Sms;
       
        /**当天从0点到24点成功出井短信**/
        set @out_day_Sms = (select count(*) from T_SendInformation where CONVERT(datetime,SignInTime,23)>=CONVERT(date,getdate(),111)  and  CONVERT(datetime,SignInTime,23)<CONVERT(date,dateadd(day,1,getdate()),111) and upDownWellSign=2 and sendSign='1' and personCard=@personCard);
        print '0点到24点成功出井短信条数：';
        print @out_day_Sms;
       
IF EXISTS (SELECT personName FROM inserted)
    BEGIN
  IF(@up_Down_Well_Sign=1 and EXISTS(select @Sms_Number1) and @Sms_Number1!='' and @rj_info=0 and @in_day_Sms=0)
   BEGIN
    INSERT INTO OUTBOX SELECT 0,'系统自动入井短信1',@Sms_Number1, '您的亲人['+@Worker_Name+']已于'+CONVERT(VARCHAR(16),SignInTime,121)+'安全入井,请您放心！[xxxxxxxxxx]', ''+(Select CONVERT(varchar(100), GETDATE(), 20))+'', 1, 0,@sms_Com_Code FROM inserted;
    IF (EXISTS (select @Sms_Number2) and @Sms_Number2!='')
     BEGIN
      INSERT INTO OUTBOX SELECT 0,'系统自动入井短信2',@Sms_Number2, '您的亲人['+@Worker_Name+']已于'+CONVERT(VARCHAR(16),SignInTime,121)+'安全入井,请您放心！[xxxxxxxxxx]', ''+(Select CONVERT(varchar(100), GETDATE(), 20))+'', 1, 0,@sms_Com_Code FROM inserted;
     END
     update T_SendInformation set sendSign='1' where sendNo=@sendNos;
   END
  
  ELSE
   IF (EXISTS(select @Sms_Number1) and @Sms_Number1!='' and @cj_info=0 and @rj_info_sex=0 and @out_day_Sms=0)
    BEGIN
     INSERT INTO OUTBOX SELECT 0,'系统自动出井短信1',@Sms_Number1, '您的亲人['+@Worker_Name+']已于'+CONVERT(VARCHAR(16),SignInTime,121)+'安全升井，请您放心！[xxxxxxxxxx]', ''+(Select CONVERT(varchar(100), GETDATE(), 20))+'', 1, 0,@sms_Com_Code FROM inserted;
     if (EXISTS (select @Sms_Number2) and @Sms_Number2!='')
      BEGIN
       INSERT INTO OUTBOX SELECT 0,'系统自动出井短信2',@Sms_Number2, '您的亲人['+@Worker_Name+']已于'+CONVERT(VARCHAR(16),SignInTime,121)+'安全升井，请您放心！[xxxxxxxxxx]', ''+(Select CONVERT(varchar(100), GETDATE(), 20))+'', 1, 0,@sms_Com_Code FROM inserted;
      END
      update T_SendInformation set sendSign='1' where sendNo=@sendNos;
    END
   
    END