How to create a an insert trigger on

inserttrigger

How to write an insert trigger for:

"If the client wants to pay by using a credit card, its details need to be entered in the record. If a credit card is not being used it must be ensured that the credit card details are blank."

create table  Management.PaymentMethods
(
    PaymentMethodID int CONSTRAINT PMID PRIMARY KEY identity(1000,24),
    Description varchar(22) constraint chkTitle check 
        (Description in ('cash','cheque','credit card'))
)



Create table Management.Payments
(
    PaymentID int CONSTRAINT PKIDD PRIMARY KEY identity(100,45),
    EventID int constraint EVId 
        foreign key(EventID) references Management.Events(EventID),
    PaymentDate date not null,
    StartDate date not null,
    CONSTRAINT PDK  CHECK (PaymentDate <= StartDate),
    check(PaymentDate > Getdate()),
    PaymentMethodID int constraint Phg 
        foreign key(PaymentMethodID) references Management.PaymentMethods(PaymentMethodID),
    CreditCardDetails varchar(100),
    ExpiryDate date constraint PD check(ExpiryDate > Getdate()),
    ChequeNo int,
    ChargePerPerson int constraint chkk check(ChargePerPerson > 0),
    NoOfPeople int not null,
    PaymentAmount as ChargePerPerson * NoOfPeople
)

Best Answer

I assume SQL Server because of the getdate(). It would be something similar to

CREATE TRIGGER Management.PaymentsCreditCard
               ON Management.Payments
               AFTER INSERT,
                     UPDATE
BEGIN
  UPDATE Management.Payments
         SET CreditCardDetails = NULL
         FROM inserted
              INNER JOIN Management.PaymentMethods
                         ON PaymentMethods.PaymentMethodID = inserted.PaymentMethodID
         WHERE Payments.PaymentID = inserted.PaymentID
               AND PaymentMethods.Description = 'credit card';
END;

. (Just to give you the idea. I haven't tested it, it might need some adaption.)

Related Question