sql server 触发器示例

触发器示例代码，根据实时数据，当监测数据大于设定警告值时，将该数据插入异常数据表中

USE [Mointor]
GO
/****** Object:  Trigger [dbo].[realtimedata_trg]    Script Date: 08/15/2013 13:20:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

  ALTER  trigger [dbo].[realtimedata_trg] 
  on [dbo].[t_pn_realtimedata]
  after insert
  as
  /**数据异常 新插入的值大于设备表t_pn_device配置的最大值**/
  if (select max_value from t_pn_device where device_code =(select device_id from inserted))<=(select monitor_value from inserted)
 
  begin
   
  declare @num int  --监测值
  select @num=monitor_value from inserted; 
  
  declare @device_id varchar(20)  --监测设备编号
  select @device_id=device_id from inserted;
  

  insert into t_oa_data_exception (id,alarm_value,device_code,exception_code,exception_style,exception_time,normal_value,pipe_code,status)
  values(newid(),@num,@device_id,'100','超出正常范围',CONVERT(varchar(100), GETDATE(), 20),'','','0');
  
  print 'exception info have been inserted into t_oa_data_exception';
  
  end
 
 

dml@2013.8.15