Effect of Trigger on Insertion of Several Records

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 2000 Forums

Transact-SQL (2000)

Effect of Trigger on Insertion of Several Records

Author

Topic

oceanboy
Starting Member

44 Posts

Posted - 2007-02-13 : 05:18:00

Hi all,

I have a table with 15 fields and when I create a new record, i will only input 5 values, the other 10 values will be retrieved by the trigger from other tables. The process will stop if the trigger can't find certain values to fill in. So every time when i insert a new record, it will do the work. BUT what if I insert a set of new records?

this scenario happens when this table is connected to Access and the user just paste the values into the grid from excel. such as

val-1 val-2 val-3 val-4 val-5 val-6 val-7 .......
aaa 111 do xxx 777 (empty)(empty)
bbb 222 re yyy 888 (empty)(empty)
ccc 333 mi zzz 999 (empty)(empty)
...
...

In this case, how does the trigger work? Does it execute once or record by record? How does SQL server read in the set of records? As a whole set or it reads from the first row to the bottom row, which in turn like adding an individual record into the table.

Thanks
OB

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-13 : 05:26:22

Why you are using trigger to do this? Why don't you bring all values and then insert them or may be bring in 5 values and then update those records with other 10 values.

Also, keep in mind that triggers does not apply on one record at a time (as a common misconception) but on a set of records.

See this: [url]http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

oceanboy
Starting Member

44 Posts

Posted - 2007-02-13 : 05:40:37

Hi harsh_athalye,

Those other 10 values need to go through some selection process. therefore, it's easier to do it by trigger.

thanks for the explanation. I think the code i have was written to work for the last row in the inserted table instead of set-based. But It is not wrong to write trigger to work for the last row, is it?

OB

Kristen
Test

22859 Posts

Posted - 2007-02-13 : 07:22:02

You need to make sure that your trigger (in fact ALL the trigger that you write!) handle multiple records.

They can use the pseudo tables DELETED and INSERTED which will contain the Before / After data of the rows which are effected.

So you need to JOIN INSERTED to your table, on the PK, and make your updates accordingly.

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-14 : 03:50:41

Yes. In the example you gave, it will execute for each Insert separately but if you are inserting bulk data using single insert statement, the trigger will fire only once for the entire insert operation.

For e.g.

-- for this, trigger will fire only once for entire batch of data
Insert Into DestTable (col1, col2, ...)
Select Col1, col2, ...
from SourceTbl
Where <some-condition>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 03:53:12

You can insert many records at once into a table

example :


insert into table1 (col1, col2, col3)
select cola, colb, colc
from   tableb

Insert trigger will execute once and the pseudo table inserted will contains more than one records.

oceanboy
Starting Member

44 Posts

Posted - 2007-02-14 : 04:03:23

yes, you are right! i forgot about it! Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources