Oninsert Trigger - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Oninsert Trigger

Author

Topic

s2002
Starting Member

49 Posts

Posted - 2007-09-25 : 18:17:04

hi,
I have 2 tables.
Products (ProductID int PK,ProductName Nvarchar,CatID int FK)
Category (CatID int pk, CatName Nvarchar, TotalProducts).

I need to create a trigger, when OnInsert in Products table, then
base on Product Category -CatID-, "TotalProducts" field in Category table Would
Increament 1.
What should be the query Structure.

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 18:30:09

Could you show us how far you got as your problem appears to be a homework question? We don't mind helping people out with their homework, but we would like to see that the poster at least tried

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

s2002
Starting Member

49 Posts

Posted - 2007-09-25 : 19:02:52

Thanks God for generosity of Peoples Like You, Who Do my HomeWorks.
Any way, My Code is below

DECLARE @ProductID AS int

SET @ProductID = (SELECT TOP 1 [Id] FROM Inserted ORDER BY [Id])
WHILE @ProductID IS NOT NULL
BEGIN
DECLARE @CategoryId int;

SELECT @CategoryId = CategoryId
FROM Inserted
WHERE ProductID = @ProductID;
BEGIN
UPDATE Category SET TotalProducts = TotalProducts + 1
WHERE CatId IN
SELECT CatId
FROM Products
WHERE ProductID=@ProductID
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-25 : 19:27:54

Could you provide a data example of what you need so that your problem is more clear?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

s2002
Starting Member

49 Posts

Posted - 2007-09-25 : 21:47:56

Thansk for your Reply tkizer,

CAtID CatName TotalProducts
1 Beverages 12
2 Condiments 12
3 Confections 13
...
..

ProductID ProductName CatID
1 Chai 1
2 Chang 1
3 Aniseed Syrup 2
...
..

What I want trigger to do is
If I insert a new Product Record into Products Table -insert into products values ("aaaa","1")-
Then since CatID=1 then "TotalProducts" Value for CAtID=1 should be
Update to 13 (12+1)
and if CatID was 3 then the TotalProducts" Value for CAtID=1 should be Update to 14(13+1)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 12:43:38

This should do it. You shouldn't need any variables or other statements as a simple join to the inserted rows gives you all that you need.

UPDATE c
SET TotalProducts = TotalProducts + 1
FROM Category c
INNER JOIN inserted i
ON c.CatID = i.CatID

You'll need to wrap it into a trigger.

I would think that your assignment would be to insert the row into Category table if a new category gets inserted into Products table, but perhaps the instructor only cares about the UPDATE statement.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-26 : 12:55:46

quote:
Originally posted by tkizer
We don't mind helping people out with their homework

We don't?

Since when?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 13:07:18

Well you have to read my whole sentence and not just quote the first part. Most homework questions that show the person is really trying get answers from us. The posts that just copy the instructor's problems verbatim here do not get answered.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 14:55:26

"the instructor's problems"

I agree with that. I have a problem with most of the Students homework in terms of the ineptitude & inexactitude of the Professor/Teacher setting the question

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-26 : 15:08:02

LMAO

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources