Solved–Lab8 Assignment: Triggers and Stored Procedures– Solution

Description

The purpose of the following assignment is to explore how triggers and stored procedures can be used to implement custom domain logic and response to speci ed changes in the database. You are encouraged to learn it yourself. Here are several helpful links:

1. PostgresSQL docs on triggers

2. Triggers and Stored Procedures

3. PostgresSQL docs on sequences

For this assignment you are given the scripts used in Lab 7 with which you should initialize your database. Rember to execute “source ./startPostgreSQL” and “source ./createPostgreDB.sh”.

Your task is to implement a trigger and procedure to automatically populate part number with incremented value upon insertion of the new row into part nyc. After that your insert statements should not include value for part number and will look like this:

I n s e r t i n t o  p a r t n y c ( s u p p l i e r , c o l o r , on hand , d e s c r )  Values  ( 0 , 0 , 2 0 , ’ Desc ’ ) ;

Implement your trigger&procedure in triggers.sql. You can test your code using test.sh

1. First create a sequence using the following SQL:

CREATE SEQUENCE p a r t n u m b e r s e q        START WITH 5 0 0 0 0 ;

2. Create a procedure that will return next value of the aforementioned sequence. Use function nextval(’part number seq’) to get the next value from the sequence.

Use the following syntax to create your procedure:

CREATE LANGUAGE p l p g s q l ;

CREATE OR REPLACE FUNCTION func name

RETURNS ” t r i g g e r ” AS

$BODY$

BEGIN

. . .

END;

$BODY$

LANGUAGE p l p g s q l VOLATILE ;

3. Use the following syntax to create a trigger calling the procedure upon insertion of the new record:

CREATE TRIGGER name f BEFORE j AFTER g f INSERT j UPDATE j DELETE g ON t a b l e FOR EACH f ROW j STATEMENT g

EXECUTE PROCEDURE funcname  (  arguments  )

1