Question

Please write SQL Query for following questions using Northwind Database:

1. Create a procedure to add a new record to Territories table. Call it NewTerr. Add the following records using the procedure.

TerritoryID	TerritoryDescription	Region
88888	Brooklyn	1
99999	Waco	4
77777	Long Beach	2

2. Create an Update Procedure that will change the Region from 1 to 3 for record with TerritoryID 88888. Call it Update88.

3. Create a Delete Procedure that will delete the one record from the Territories table that has Waco in the TerritoryDescription.

4. Define a trigger. Give an example when it should be used.

Answer 1

SQL Solution

Stored Procedures
Stored procedures are set of sql statements(block) to perform some actions on database and which can be called from various applications.

Syntax of Stored Procedure:
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
AS   
Declaration section
BEGIN   
Execution section

END

The following SQL statements creates stored procedures that insert,update and delete records to/from the Territories table:

1.
CREATE OR REPLACE PROCEDURE NewTerr (TerrID INT, TerrDescr varchar(50),region INT)
AS
BEGIN
insert into Territories values(TerrID,TerrDescr,region);
END

2.
CREATE OR REPLACE PROCEDURE Update88
AS
BEGIN
Update Territories set Region=3 where TerritoryID=88888 and Region=1;
END
3.
CREATE OR REPLACE PROCEDURE deletewaco
AS
BEGIN
Delete from Territories where TerritoryDescription='Waco';

END

Triggers
Triggers are special type of stored procedures that executes automatically when specific actions occur in database like insert, update or delete records.
Triggers can be used 'BEFORE','AFTER' or 'INSTEADOF' any SQL actions like 'INSERT','DELETE' or 'UPDATE' records.

Eg;-
Suppose you have another table 'Terrhistory ' to store action history on Territories table: then
to store history on insert record we can create a trigger:

Eg1.
CREATE OR REPLACE TRIGGER TerrAction
BEFORE INSERT ON Territories
FOR EACH ROW  
WHEN (NEW.TerritoryID > 0)  

BEGIN  
insert into Terrhistory values(NEW.TerritoryID,'ADDED')
END;  

Eg2. Before delete record

CREATE OR REPLACE TRIGGER TerrActionDel
BEFORE DELETE ON Territories
FOR EACH ROW  
WHEN (NEW.TerritoryID > 0)  

BEGIN  
insert into Terrhistory values(NEW.TerritoryID,'DELETED')
END;