Trigger insert problem - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Trigger insert problem

Author

Topic

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-08 : 08:45:06

Hi,

I am inserting 8 values into a table that are coming from another table.

insert into PriceList
select @ID, Name, Cost
from PriceListDefaults

The table PriceList has a trigger for INSERT. It's only firing once for the above statement.

Should it not fire 8 times ? (because i'm doing 8 inserts from the other table)

Thanks for any advice

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-12-08 : 08:48:47

The trigger can handle more than one row at a time so it seems to be ok if the trigger fires only one time.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 08:50:19

nope it will be fired only once with all 8 records in inserted table and processed as a batch

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

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-08 : 08:51:09

Thank you.

Is there a way to make it fire each time ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 08:53:26

why do you need that? it will do processing of all the 8 rows together so there's no need of calling it repeatedly

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

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-08 : 08:54:32

I'm creating a mapping table that needs the unique identity of each of the 8 rows.

My trigger does an insert into this mapping table. But it's only doing one insert at the moment, where i need it to do 8

Maybe a trigger is not suitable for this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 09:02:42

quote:
Originally posted by grimmus

I'm creating a mapping table that needs the unique identity of each of the 8 rows.

My trigger does an insert into this mapping table. But it's only doing one insert at the moment, where i need it to do 8

Maybe a trigger is not suitable for this

what all should mapping table contain?

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

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-08 : 09:13:23

I've explained this in another post i created -

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168903

Basically, there's 8 values for each price list. But there are 3 price list categories. So each price list in each category has 8 values.

I need to way to relate the values in different categories to each other so that when one's updated the other ones are updated too.

Thanks for any tips. I hope i am being clear.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 09:17:26

there's problem in this. how do you relate between the values in mapping table? how do you determine which all values need to be put together?

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

grimmus
Yak Posting Veteran

53 Posts

Posted - 2011-12-08 : 09:22:07

That's the issue I'm having.

I was thinking after the first insert all the identities could be added to a temp table 1

Then, the second insert identities could be added to temp table 2

Finally ,the third insert identities could be added to temp table 3

Then, somehow i could merge these columns together into one table (no idea if this is possible). There will always be 8 rows insert each time for the 3 categories so matching them up shouldn't be an issue.

Can you suggest a less messy solution ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 09:30:31

i think you should have an identity column (or a pk column) in your main table to row for which you add the values against various category

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-12-08 : 10:00:23

Perhaps an in depth explanation on what you are trying to do is in order. Follow the HOW TO ASK link and provide the information that is requested, and Visakh will come up with an answer full of CROSS APPLYS for you.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 10:52:02

quote:
Originally posted by DonAtWork

Perhaps an in depth explanation on what you are trying to do is in order. Follow the HOW TO ASK link and provide the information that is requested, and Visakh will come up with an answer full of CROSS APPLYS for you.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

I've to live upto my reputation as suggested by my custom title

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources