Need help with VIEW query, Trigger and Stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Need help with VIEW query, Trigger and Stored proc

Author

Topic

sweetpepper66
Starting Member

10 Posts

Posted - 2011-12-15 : 07:56:45

I have 2 tables Category and Product. Columns of Category table are cid, descritption. Columns of Product table are pid, description, cid,price and size. I have to 1-Create and execute a view named EXAM_VIEW that shows all columns from both tables. Use an inner join.
Here is what I have so far for this one:
CREATE VIEW EXAM_VIEW
AS
SELECT cid, description, pid, price, size
FROM Product INNER JOIN Category

If I do INNER JOIN the query don't work, what did I do wrong?

2 - I have to Create a database trigger named EXAM_TRIGGER that prevents a user from deleting a Product record during store hours (6 a.m. – 11 p.m.). Display an appropriate error message. Create and execute a test to show that the trigger is working properly.
Here is what I have so far
CREATE TRIGGER EXAM_TRIGGER
ON Product
AFTER DELETE
AS
BEGIN
If exists (select * from deleted where cid = 'store hours'
BEGIN
raiserror ('Cannot delete Product records during store hours (6a.m - 11 p.m.', 11,1)
rollback tran
END
END

And the last: I am having a total blank for this one.
3 -Create a stored procedure named SP_EXAM that will be used to insert records into the Product table. Create and execute several tests to show that the procedure is working properly.

I would really appreciate any help or suggestion
Thank you in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 08:02:20

where's condition for inner join? you've specify the relationship based on which it needs to join using an ON


CREATE VIEW EXAM_VIEW
AS
SELECT Category.cid, description, pid, price, size
FROM Product INNER JOIN Category
ON Category.cid = Product.cid

2. why do you need a trigger for this? it can more easily implemeted at your front end application

3.whats the issue with procedure? have a look at CREATE PROCEDURE statement and start on it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources