insert 觸發器例子

 GO

IF EXISTS( SELECT name FROM sysobjects WHERE name='TrgChargeInsert' AND type='TR')
  DROP TRIGGER TrgChargeInsert
GO
RAISERROR ('CREATE TRIGGER: TrgChargeInsert',0,1) WITH NOWAIT
GO
CREATE TRIGGER TrgChargeInsert ON CardDealRecordConsume FOR INSERT
AS
  IF @@rowcount = 0
  RETURN
  
  SET NOCOUNT ON
  
  DECLARE @CustomerID INT, @MobilePhone VARCHAR(20), @DealAmount Money, @ArisesTime DATETIME, @BookID INT,@PhysicalCode CHAR(10),@CardID INT,@SendMessage VARCHAR(255)

  IF EXISTS(SELECT * FROM Inserted where BookID in(101,102,103,104,105))
  
  BEGIN
  SELECT @CustomerID=CustomerID,@DealAmount=DealAmount,@ArisesTime=ArisesTime,@PhysicalCode=PhysicalCode,@CardID=CardID FROM Inserted where BookID in(101,102,103,104,105)
  SELECT @MobilePhone = MobilePhone
  FROM VwCustomer WHERE PhysicalCode = @PhysicalCode
  IF @MobilePhone IS NOT NULL
  BEGIN
  SET @SendMessage = '您好！您的校園卡於 ' 
+ DATENAME(YEAR, @ArisesTime) + '年' + DATENAME(MONTH, @ArisesTime) + '月' + DATENAME(DAY, @ArisesTime) + '日 ' + DATENAME(HOUR, @ArisesTime) + '時' + DATENAME(MINUTE, @ArisesTime) + '分' + ' 儲值了 ' + CAST(@DealAmount AS VARCHAR(8)) + ' 元'
   
 INSERT INTO SmsMessage(ArisesTime, PhysicalCode, CardID, CustomerID, MobilePhone, SendMessage)
  VALUES(@ArisesTime, @PhysicalCode, @CardID, @CustomerID, @MobilePhone, @SendMessage)
  END

  END