Question

1. Assume the TRIP table contains a column named TotalPersons that represents the total number of persons for that trip. Following the style shown in the class PowerPoint, write the T-SQL trigger code for the situations given below.  You will not be able to test these triggers without the TotalPersons column.

1. When inserting a row in the RESERVATION table, add the NumPersons value to the TotalPersons value for the appropriate trip.
2. When updating a row in the RESERVATION table, add the difference between the new NumPersons value and the old NumPersons value to the TotalPersons value for the appropriate trip.
3. When deleting a row in the RESERVATION table, subtract the NumPersons value from the TotalPersons value for the appropriate trip.

Answer 1

1) Inserting Row

CREATE TRIGGER ins_sum  BEFORE INSERT ON reservation -> FOR EACH ROW SET @Totalpersons = @totalperson + NEW.numperson;

Output -> Number of total person value for appropriate-trip tabele is increase.

2) Updating Row

CREATE TRIGGER ins_sub AFTER UPDATE ON reservation -> FOR EACH ROW SET @Totalpersons = NEW.numperson - OLD.numperson;

Output -> Number if total person will be update through new value

3) Deleting Row

CREATE TRIGGER ins_sub AFTER UPDATE ON reservation -> FOR EACH ROW SET @Totalpersons = NEW.numperson - Totalpersons;

Output -> Number of total person value reduce for appropriate-trip tabele.