Need help with triggers - 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

Need help with triggers

Author

Topic

michelleNoby
Starting Member

12 Posts

Posted - 2006-04-24 : 23:01:29

Hi, I am quite new to sql and i need a solution to this problem with triggers.
Here's the question.
Create a DELETE trigger on the NewProducts table. This trigger determines whether an order history exists in the Order Details table before the trigger permits a deletion from the NewProducts table. NewProducts was created in the previous questions. The created trigger determines whether an order history exists for a product
being deleted. If the product has never been ordered, then the product can be deleted. If the product has a history of orders then the delete from the product table is rolled back and the trigger is to return a custom error message.
(I dont even know how to start this)

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-24 : 23:17:06

quote:
Originally posted by michelleNoby

...NewProducts was created in the previous questions...

Hmmmm...what makes me think this is a plea for homework help? Hmmm....

quote:
Originally posted by michelleNoby

...(I dont even know how to start this)...

You could start with lesson 1 from the beginning of the semester.

This type of problem can often be solved by a technique known as an "NZDF" solution among RDBMS SQL developers. I'm not that good with writing NZDF functions, but hopefully someone else on the forum can supply you with one...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-24 : 23:23:10

I suggest don't go for triggers,
create an sproc where the user can issue the delete and do the checking there

if you have no control over the table, like users can directly make deletions, then hope the code below helps, read more in BOL for create triggers...

create trigger nameofyourtrigger on dbo.newproducts
instead of delete
as

if exists(select * from OrderHistoryTable where productID in (select productID from deleted))
--do whatever you need to do like delete
else
--raise an error message, see raiserror in BOL

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-25 : 02:45:42

"a technique known as an "NZDF" solution among RDBMS SQL developers."
Pardon my ignorance. But what is NZDF ?

Kristen
Test

22859 Posts

Posted - 2006-04-25 : 04:51:22

Definition: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62702&whichpage=2
Prior art: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64273

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-25 : 07:05:13

Thank you for the enlightenment

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-25 : 08:10:51

Found the source on NZDF http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1152742,00.html

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-25 : 09:30:10

quote:
Originally posted by jen

I suggest don't go for triggers,
create an sproc where the user can issue the delete and do the checking there

The ASSIGNMENT was to write a trigger...
Of course, the intention of the ASSIGNMENT was probably also for the students to do the work THEMSELVES.

michelleNoby
Starting Member

12 Posts

Posted - 2006-04-25 : 16:25:42

wow, i didnt know this site is quite friendly and helpful, i cant blame you tho, you all have been abused by others who DO NOT WANNA LEARN. But thank you anyways for your comments and all your help. Appreciate your kindness!

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-04-26 : 11:28:39

Michelle,

I think Jen's post will get you going in the right direction. In the future consider rewording your question in a way that doesn't make it so obvious that it is homework. The following is kind of a dead give away:

quote:
NewProducts was created in the previous questions.

Phrase your question like "I have this table 'at work' that is called NewProducts. I also have a table called OrderDetails that contains a history of products that were ordered. I want to write a trigger that won't allow a product to be deleted if the product has already been ordered."

The table name "NewProducts" will still give it away, but you get the idea. Just show folks that you've at least put some thought into what is being asked for instead of just cutting/pasting a question.

Of course this thread has now become a forum for a discussion on the purpose of homework/assignments. My thought is that assignments are to get you to learn. If you blindly copy the answers from this forum (or others) then you learn nothing. But if take answers like Jen (or the complete solution) and then review how it was done then you will have learned. The fact that you indicated you were clueless as to how to get started either implies that you have a cruddy instructor who basically didn't learn how to teach and is simply posting questions out of a book, or that you have slept through (or skipped class) all together.

Good luck,
Dalton

PS - Do continue to post. If you are serious about learning (I'll assume rotten prof) you wont' find a more knowledgable base of folks to learn from.

Kristen
Test

22859 Posts

Posted - 2006-04-26 : 15:31:30

druer: Do you reckon that Michelle Noby is a made up name? Or that Michelle's Prof is not checking sites like this? Nah, didn't think you did!, so Michelle meets with a lukewarm reception here, and Michelle's Prof knows that Michelle didn't struggle through the assignment unaided ...

Michelle: If that seems harsh then you should know that I'm always happy to help, but that's on the basis of someone asking specific questions about particular aspects that they are stuck on, not asking me to do their work for then for free - I spend my days getting paid to do peoples work for them, I'm not about to start doing that for free!

Kristen

michelleNoby
Starting Member

12 Posts

Posted - 2006-04-26 : 16:59:38

^
I didnt ask anybody to start "doing" all my homework,and i wasnt expecting for someone to do the coding from start to finish.
All i wanted to ask is ideas how to start this kind of question. thats why i gave the comment (idont even know how to start this) but instead of giving me ideas on how to start, you guys jump the gun on me and throwing NDZF stuff on my face (well not literally). I dont care what kind of job you do be it a stripper or a professional DBA for all i care, im here to ask questions and not for YOU or anyone else to do my homework for FREE.

michelleNoby
Starting Member

12 Posts

Posted - 2006-04-26 : 21:01:15

----ok so here's what I came up with-----

create trigger trg_NewProduct_Delete
On NewProducts
For delete
AS
Declare @prodID INT, @rowcount INT
set @prodID=1
set @rowcount = 0
/*Begin loop*/
While @prodID < 77
Begin
set @prodID = @rowcount
set @rowcount = @rowcount + 1
Select @rowcount = count (*) from Orderdetails
where ProductID=@prodID

If @rowcount = 0
Delete NewProducts where ProductID=@prodID
If @rowcount <> 0
Begin
Raiserror ('Product cannot be deleted', 12,0)
Rollback transaction
END
End

when i execute:

delete newproducts where productid=2

the loop looks like its not stopping, i have to hit the stop button and i get the "0 rows affected" line by line...any suggestions why?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-26 : 21:20:36

try to analyze what you are doing with the loop and increments,
check those in red text and add a stop criteria (in blue) for the loop

and what exactly are you doing for this delete trigger?
you don't need a loop, just a where clause in your delete query
and remember that you're deleting on newproduct that has a loop for deleting... kinda recursive?

quote:
Originally posted by michelleNoby

----ok so here's what I came up with-----

create trigger trg_NewProduct_Delete
On NewProducts
For delete
AS
Declare @prodID INT, @rowcount INT
set @prodID=1
set @rowcount = 0
/*Begin loop*/
While @prodID < 77
Begin
set @prodID = @rowcount
set @rowcount = @rowcount + 1
Select @rowcount = count (*) from Orderdetails
where ProductID=@prodID

If @rowcount = 0
Delete NewProducts where ProductID=@prodID
If @rowcount <> 0
Begin
Raiserror ('Product cannot be deleted', 12,0)
Rollback transaction
set @prodid=77
END
End

when i execute:

delete newproducts where productid=2

the loop looks like its not stopping, i have to hit the stop button and i get the "0 rows affected" line by line...any suggestions why?

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 21:37:00

"This trigger determines whether an order history exists in the Order Details table before the trigger permits a deletion from the NewProducts table"
you can use exists to determine whether the product exists in the table

Basically you don't need to use any loop to accomplish this.

Another thing you should know is the inserted & deleted table. You can find out more infomation on these from the SQL Server Books Online. deleted table will contains the deleted records from the DELETE command.

So putting all these together


IF EXISTS 
(
    SELECT * FROM deleted d inner join OrderDetails o
    ON d.product = o.product
)
BEGIN
    Raiserror ('Product cannot be deleted', 12,0)
    Rollback transaction
END

michelleNoby
Starting Member

12 Posts

Posted - 2006-04-26 : 22:22:21

^
oh god i went thru all that trouble with looping and just a simple if statement solves it?? LOL
Thanks.
(But the looping will work too, am i right?)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 22:35:36

quote:
Originally posted by michelleNoby

^
oh god i went thru all that trouble with looping and just a simple if statement solves it?? LOL
Thanks.
(But the looping will work too, am i right?)

Using a loop when you don't need to is a very bad practice. It is much slower and can kill the performance of a system. A trigger especially should do the minimum necessary.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 23:21:04

"(But the looping will work too, am i right?)"

1. You are assuming ProductID ranges from 1 to 77. It may be true only for your current sets of data.

2. "Delete NewProducts where ProductID=@prodID".
Your trigger is a AFTER Trigger which means it is fired AFTER the records has been deleted. So the ProductID = 2 (Refering to this "delete newproducts where productid=2") will not be in the product table at this point of time.

3. By looping from 1 to 77, you are deleting all productid that has no order history (not in orderdetails table) which is not what you want.

michelleNoby
Starting Member

12 Posts

Posted - 2006-04-26 : 23:23:55

wow i am learning from you, better than my prof.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-26 : 23:28:37

The best way to learn something is by doing it yourself which is what you have done.

michelleNoby
Starting Member

12 Posts

Posted - 2006-04-26 : 23:29:42

not w/out your help :D