Task#07 SP & Triggers Solution

$30.00

Description

  1. Create a stored procedure with at least two arguments (can be more than two), one for input and one for output. Write PHP code to execute the procedure and use the input and output parameters.

  1. Consider the following tables:

Table 1: Users [user_id (p.k)]

user_id

username

password

email

1

abc123

def321

abc123@yahoo.com

2

xyz789

vbn123

xyz789@gmail.com

Table 2: Summary [id(p.k)]

id

total_users

yahoo

hotmail

gmail

1

2

1

0

1

  1. Create a stored procedure to insert data into the users table. The values to be stored must be passed as parameters to the stored procedure.

  1. Create trigger(s) to update the records in the summary table. The summary table will contain only one record and will be updated each time a new entry is made to the users table, or an email address is updated in the users table or a user is deleted from the users table.

Insert data in the users table (at least 10 records) with different email addresses using the stored procedure created in (a). Check the summary table each time you add a new user, update a user’s email address or delete a user to check if the trigger(s) are working correctly.

1 | P a g e


error: Content is protected !!