Question

Pretend you are writing a SQL Code for a database with a Homeowners Insurance Company.

Use Microsoft SQL to write a code for the following:

1. Create a new table called Policy_Total. It will contain the number of policies in the policy table. Create a trigger that increments the policy total every time a policy is added. Create another trigger that decrements the policy total every time a policy is deleted.

2. Create a stored procedure that compares the policy total field from the Policy Total Table to the number of policies in the Policy Table.

Answer 1

Below SQL code will create the table called Policy_Total:

CREATE TABLE Policy_Total(
   Num_Policies INT
);

Trigger to increment the policies:

CREATE TRIGGER IncrementPolicy
       ON Policy
AFTER INSERT
AS
BEGIN
       SET NOCOUNT ON;

       UPDATE Policy_Total
       SET Num_Policies = Num_Policies + 1;
END

Trigger to decrement the policies:

CREATE TRIGGER DecrementPolicy
       ON Policy
AFTER DELETE
AS
BEGIN
       SET NOCOUNT ON;

       UPDATE Policy_Total
       SET Num_Policies = Num_Policies - 1;
END

Stored Procedure to compare total policies

CREATE PROCEDURE comparePolicies
AS
   IF (SELECT COUNT(*) FROM Policy) = (SELECT Num_Policies FROM Policy_Total)
       SELECT 'Correct Policies'
   ELSE
       SELECT 'Incorrect Policies'
GO