User Tools

Site Tools


notes:sql:triggers

SQL Triggers

The following code snippets are just examples of the SQL triggers. They are not ready-to-go scripts.

Example #1:

CREATE TRIGGER TRG_Tickets_RefundCancelled ON dbo.Tickets
FOR UPDATE
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @IsRefund BIT, @IsCancelled BIT, @RefundCaptured BIT
 
  IF UPDATE(IsCancelled)
  BEGIN
    SELECT @IsCancelled=IsCancelled FROM INSERTED
    SELECT @IsRefund=IsRefund FROM INSERTED
    SELECT @RefundCaptured=RefundCaptured FROM DELETED
    IF @RefundCaptured=1    
    BEGIN
      RAISERROR ('This ticket has been refunded already. You cannot change the "Cancelled" status.',17,1)
      ROLLBACK TRANSACTION
    END
    IF @IsCancelled=0 AND @IsRefund=1
    BEGIN
      RAISERROR ('This ticket has not been cancelled yet. You have to cancel the ticket first.',17,1)
      ROLLBACK TRANSACTION
    END
 
  END
 
  IF UPDATE(IsRefund)
  BEGIN
    SELECT @IsCancelled=IsCancelled FROM INSERTED
    SELECT @IsRefund=IsRefund FROM INSERTED
    SELECT @RefundCaptured=RefundCaptured FROM DELETED
    IF @RefundCaptured=1
    BEGIN
      RAISERROR ('This ticket has been refunded already. You cannot change the "Refund" status.',17,1)
      ROLLBACK TRANSACTION
    END
    IF @IsCancelled=0 AND @IsRefund=1
    BEGIN
      RAISERROR ('This ticket has not been cancelled yet. You have to cancel the ticket first.',17,1)
      ROLLBACK TRANSACTION
    END
  END
  SET NOCOUNT OFF
  RETURN
END
GO

Example #2:

CREATE TRIGGER TRG_CheckReissuedTicket ON dbo.Tickets 
FOR UPDATE
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @ReissuedBatch INT, @IsReissued BIT
 
  IF UPDATE(IsReissued)
  BEGIN
    SELECT @ReissuedBatch=ReissuedBatch FROM DELETED
    IF @ReissuedBatch != 0
    BEGIN
      RAISERROR ('This ticket has been printed already. You cannot uncheck the "Reissued" status.',17,1)
      ROLLBACK TRANSACTION
    END
  END
  IF UPDATE(ReissuedBatch)
  BEGIN
    SELECT @IsReissued=IsReissued FROM DELETED
    IF @IsReissued=0
    BEGIN
      RAISERROR ('This ticket should be checked to reissue at first. You cannot set a batch number.',17,1)
      ROLLBACK TRANSACTION
    END
  END
  SET NOCOUNT OFF
  RETURN
END
GO

Example #3:

CREATE TRIGGER TRG_OrdersNSF ON [dbo].[Orders] 
FOR UPDATE
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @OldNSF BIT, @NewNSF BIT, @PaymentMethod INT
 
  IF UPDATE(ChequeNSF) 
  BEGIN
    SELECT @PaymentMethod=PaymentMethod FROM INSERTED
    IF @PaymentMethod=0
    BEGIN
      RAISERROR ('Credit card orders cannot have the NSF status.',16,1)
      ROLLBACK TRANSACTION
    END
    ELSE
    BEGIN
      SELECT @OldNSF=ChequeNSF FROM DELETED
      SELECT @NewNSF=ChequeNSF FROM INSERTED
      IF @OldNSF<>@NewNSF
      BEGIN
        RAISERROR ('You cannot change the NSF status.',16,1)
        ROLLBACK TRANSACTION
      END
    END
  END
  SET NOCOUNT OFF
  RETURN
END
GO

Example #4:

-- Test
INSERT Orders DEFAULT VALUES
DELETE Customers WHERE CustomerID='ALFKI'
 
-- Create a custom error message
sp_addmessage 60001,16,'Value for field %s.%s must have matching value %s.%s'
 
-- Create a child trigger
ALTER TRIGGER OrderHasCustomer
ON Orders
FOR INSERT, UPDATE
AS
  IF EXISTS (SELECT 'True' 
             FROM Inserted INS 
             LEFT JOIN Customers CUS 
             ON INS.CustomerID=CUS.CustomerID
             WHERE CUS.CustomerID IS NULL)
  BEGIN
    RAISERROR(60001,16,1,'Orders','CustomerID','Customers','CustomerID')
    ROLLBACK TRAN
  END
 
-- Create a parent trigger
CREATE TRIGGER CustomerHasOrders
ON Customers
FOR DELETE
AS
  IF EXISTS (SELECT 'True'
             FROM Deleted DEL
             JOIN Orders ORD
             ON DEL.CustomerID=ORD.CustomerID)
  BEGIN
    RAISERROR('The customer has orders',16,1)
    ROLLBACK TRAN
  END
notes/sql/triggers.txt · Last modified: 2015/06/24 by admin