This table should have all columns the products table

Chapter 15

How to code stored procedures, functions, and triggers

procedure should have one parameter for the category name.

Code at least two EXEC statements that test this procedure. (Note that this table doesn’t allow

this function should accept one parameter for the item ID, and it should return the value of the

discount price for that item.

GO

PRINT'Discount price: $'+
CONVERT(varchar, dbo.fnDiscountPrice(2),1);

created in exercise 2, and it should return the value of the total for that item.

USE MyGuitarShop
GO
CREATEFUNCTION fnItemTotal
(@ItemID INT)

3 Write a script that creates and calls a stored procedure named spInsertProduct that inserts a row

into the Products table. This stored procedure should accept five parameters. One parameter for

that indicates that this column doesn’t accept negative numbers. Similarly, the procedure should

raise an error if the value for the DiscountPercent column is a negative number.

BEGIN

updates the DiscountPercent column in the Products table. This procedure should have one

parameter for the product ID and another for the discount percent.

CREATEPROC spUpdateProductDiscount(
@ProductID INT,
@DiscountPercent INT

EXEC spUpdateProductDiscount3,15;

by multiplying it by 100. That way, a discount percent of .2 becomes 20.

Test this trigger with an appropriate UPDATE statement.

GO

CREATETRIGGER Products_Insert

SET DateAdded =GETDATE()

WHERE DateAdded =NULL;

the Description column. Also, it should have an AuditID column for its primary key, and the

DateAdded column should be changed to DateUpdated.

GO

CREATETABLE ProductsAudit(

ListPrice MONEY NOTNULL,

DiscountPercent MONEY NOTNULLDEFAULT 0.00,