triggers in sql 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

how to activate triggers for multiple rows inserted.

i have defined a trigger which work for only 1 row inserted which is as follows:

SQL

create trigger userid
on users
after insert
as
DECLARE @name varchar(50), @id int
begin
select @id= (select Max(id) from users)
select @uname = uname from inserted 
update users set id = id +1 where uname = @uname
end

please edit this trigger so that it can work for multiple rows inserted.
or give any suitable example.

--------------------------------

[Later addition] Case 2:

SQL

create trigger manage_mail
on mymail
after delete
as
DECLARE @count_references int,@mail_id varchar(20)
begin
   select @mail_id=mail_id from deleted
   select @count_references=count(*) from mymail where

   mail_id=@mail_id
   if(@count_references =0)
   begin
      delete from mails where mail_id = @mail_id
   end
end

Posted 5-Sep-11 10:25am

Sachin gulati

Updated 5-Sep-11 11:40am

Wendelius

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2011-09-05T10:41:00

Solution 1

It looks like you're trying to generate a unique id for each user by yourself. Don't do this. Instead, define IDENTITY for ID column. For more info on that, see: IDENTITY (Property) (Transact-SQL)[^].

By using IDENTITY the management system gives automatically a unique value for each new user. You can also use this value as the primary key for the table.

The trigger itself should be modified so that you would for example use a cursor for each row in inserted table and update the users table correspondingly, but as said, this would be a wrong way to go.

Posted 5-Sep-11 10:41am

Wendelius

Comments

Sachin gulati 5-Sep-11 17:00pm

hey thanx for the brilliant suggestion.... :)

Wendelius 5-Sep-11 17:04pm

You're welcome :)

Sachin gulati 5-Sep-11 17:28pm

hey but still i need to define a trigger which will work for multiple rows for some other purpose. please guide me...

Wendelius 5-Sep-11 17:29pm

Do yo have an example about the other case?

Sachin gulati 5-Sep-11 17:34pm

create trigger manage_mail
on mymail
after delete
as
DECLARE @count_references int,@mail_id varchar(20)
begin
select @mail_id=mail_id from deleted
select @count_references=count(*) from mymail where

mail_id=@mail_id
if(@count_references =0)
begin
delete from mails where mail_id = @mail_id
end
end

in this i m checking the reference for the mails(table) in mymail(table) on deletion in mymail if it is 0 then the original mail from the table mails will also be deleted...

Wendelius · Accepted Answer · 2011-09-05T11:54:00

Solution 2

Ok, I'm adding a new solution since this is a different situation.

Anyhow, if I understand this correctly you want to delete the row in mails if no more mymails rows are referring to it. And the mailid is the same in both tables so it's a foreign key. So you're deleting the parent row when no more children exist.

If that is correct, the code could be something like:

SQL

create trigger manage_mail
on mymail
after delete
as
begin
   delete from mails
   where  mails.mailid in (select d.mailid 
                           from deleted d)
   and not exists (select 1
                   from mymail mm
                   where mm.mailid = mails.mailid);
end

Test this carefully (inside a transaction) before you use it.

But basically this should delete all the mails rows which have the same id as in deleted rows and no more children exist.

Posted 5-Sep-11 11:54am

Wendelius

Comments

Sachin gulati 5-Sep-11 18:03pm

hey thanx its working...
n what if i want to delete all children rows if parent is going to be deleted?

Wendelius 5-Sep-11 18:06pm

In that case I wouldn't do it using triggers but using a foreign key. See: http://msdn.microsoft.com/en-us/library/ms175464.aspx[^]

Sachin gulati 5-Sep-11 18:15pm

there is no suggestion for doing it....
anyways thanx alot.... :)

Wendelius 5-Sep-11 18:23pm

No problem :)

When you have time, go through the concepts. And in the CREATE TABLE syntax, check for foreign key definition. For example if you have two tables, Class and Participant, you can define a foreign key on participant to point to class. If this foreign key is defined with ON DELETE CASCADE it would mean that when the class is deleted, all participants are deleted also. There are also other choices for the delete (and update) actions...

And if you like, you can mark the answers as accepted.

Sachin gulati 5-Sep-11 20:50pm

thanx i studied about the on delete cascade, n it worked as u said.....
now i have no more queries in this context. i m fully satisfied...
thankew :)

Wendelius 6-Sep-11 0:19am

You're welcome :)