How to call a table to change the value of another table column

Question

I have created three tables.

CREATE TABLE Clients
(
    ClientID INT IDENTITY(1,1) PRIMARY KEY,
    First_Name VARCHAR(50) NOT NULL,
    Last_Name VARCHAR(50) NOT NULL,
)
CREATE TABLE Reservation
(
    ReservationID INT IDENTITY(1,1) PRIMARY KEY,
    ClientID INT FOREIGN KEY (ClientID) REFERENCES Clients(ClientID),
    Reservation_paid VARCHAR(3) DEFAULT 'NO',
)
CREATE TABLE Payment
(
    Payment_ID INT IDENTITY(1,1) PRIMARY KEY,
    ClientID INT FOREIGN KEY (ClientID) REFERENCES Clients(ClientID),
    ReservationID INT FOREIGN KEY (ReservationID) REFERENCES Reservation(ReservationID),
)

I would like to change the value of the Reservation_paid column to YES in the reservation table whenever the Client actually pays for the reservation, and I want to do this automatically using a trigger.

Example. If ClientIDthe reservation table automatically exists in the Payment table, the value of Reservation_paid will be set to YES. Thank you in advance.

Answer 1

CREATE TRIGGER trgAfterInsert ON [dbo].[Payment] 
FOR INSERT
AS
declare @ClientID int;
select @ClientID =i.ClientID from inserted i;   

if update(ClientID)
    UPDATE Reservation set Reservation_paid='Yes' WHERE  
    ClientID=@ClientID;

- PRINTING 'AFTER INSERT starts.'

Answer 2

After Insert Trigger should do something like this

UPDATE R 
SET    Reservation_paid = 'Yes' 
FROM   reservation R 
WHERE  EXISTS (SELECT 1 
               FROM   INSERTED I 
               WHERE  I.clientid = R.clientid 
                      AND I.reservationid = R.reservationid) 

Answer 3

Write a trigger that will work in the table Reservationafter any column insertor updateon the ClientIdtable Payment. Then map the column ClientIdto the ClientIdtable column Reservationand update the corresponding Reservation_paidto YES.

Edit: The trigger will look like this:

CREATE TRIGGER `UpdateReservation_paid` AFTER INSERT OR UPDATE ON `Payment`
 FOR EACH ROW BEGIN
 AS 
begin
 update Reservation 
 SET Reservation_paid='YES'
 Where NEW.ClientID = Reservation.ClientID 
       and NEW.ReservationID = Reservation.ReservationID   
end

Answer 4

CREATE TRIGGER trgAfterInsert ON [dbo].[Payment] 
FOR INSERT
AS
declare @ClientID int;
select @ClientID =i.ClientID from inserted i;   
insert into Reservation(ClientID,Reservation_paid)    
values(@ClientID,'Yes');

--PRINT 'AFTER INSERT trigger fired.'

GO