SQL 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

SQL Trigger

Author

Topic

lkr
Starting Member

3 Posts

Posted - 2011-12-17 : 17:45:33

Hii,
I am new to SQL programming and triggers. Here its one of my sample trigger

create trigger student_trigger on Student after insert as
begin
declare @grade real;
declare @sid int;
select @grade=i.GRADE from inserted i;
select @sid=i.SID from inserted i;
if(@grade >6 and @grade<10)
begin
insert into Apply values(@sid,'College1','Biology');
insert into Apply values(@sid,'College2','Geology');
end
end

But when i insert a bunch of records ,eg:
insert Student select SID+1,sname,GRADE from Student

its not getting inserted to Apply all those records which satisfy the condition..If anyone give me a solution with explanation I am so thankful..

Thank You
lkr

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-17 : 18:07:55

The trigger gets called only once for one insert statement, no matter how many records get inserted. So what you need to do is something like this:


INSERT INTO [Apply]
SELECT INSERTED.[Sid], 'College1','Biology'
WHERE INSERTED.GRADE > 6 AND INSERTED.GRADE < 10;

INSERT INTO [Apply]
SELECT INSERTED.[Sid], 'College2',Geology'
WHERE INSERTED.GRADE > 6 AND INSERTED.GRADE < 10;

I say something like because I did not quite follow your logic in having two insert statements with College1 and College2.

lkr
Starting Member

3 Posts

Posted - 2011-12-17 : 19:28:14

Sunitabeck,
Thank you for an immediate reponse.
I am using SQL Server2005. I am getting an error when I directly use 'inserted' table for data manipulation, as
"The multi-part identifier "inserted.GPA" could not be bound"

Actually I would like to know How can I rewrite the previous trigger example to trigger for each row for a single insert statement?

If I do the same for delete operation it also wont give exact results..

Thank You
lkr

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-12-17 : 19:38:49

You can't. SQL triggers fire once per statement, not once per row. There's no way to make them fire for each row, you must write your triggers so that they can handle any number of rows in the inserted and deleted tables.

Add the following to both of sunitabeck's queries (as the 3rd line), but note that it's still 'something like'

FROM inserted

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 01:06:48

quote:
Originally posted by lkr

Sunitabeck,
Thank you for an immediate reponse.
I am using SQL Server2005. I am getting an error when I directly use 'inserted' table for data manipulation, as
"The multi-part identifier "inserted.GPA" could not be bound"

Actually I would like to know How can I rewrite the previous trigger example to trigger for each row for a single insert statement?

If I do the same for delete operation it also wont give exact results..

Thank You
lkr


create trigger student_trigger 
on Student 
after insert as
begin
insert into Apply 
select i.SID,t.college,t.subject
from inserted i
cross join
(select 'College1' as college,'Biology' as subject
 union all
 select 'College2','Geology'
 )t
where i.GRADE >6 and i.GRADE<10
end

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

lkr
Starting Member

3 Posts

Posted - 2011-12-18 : 12:59:39

Hi Visakh
Thanks a lot...your query works fine..

@ Sunitabeck
simple changes to your query also get executed..

Thanks a lot ....

lkr

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 23:43:19

quote:
Originally posted by lkr

Hi Visakh
Thanks a lot...your query works fine..

@ Sunitabeck
simple changes to your query also get executed..

Thanks a lot ....

lkr

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources