How to create a an insert trigger on

Question

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
)

• McNets: Please tag your DBMS (version included).

Answer 1

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.)