Difference in creating triggers on views in SQL Server -

i using sql server 2012.

the view definition is:

create view dbo.emp    select         e.eid, e.enm, p.fnm, p.lnm             employee e     inner join         person p on e.eid = p.id go 

the first trigger definition is:

create trigger emptrgg on emp instead of insert  begin      insert person(id, fnm, lnm)        select             id, fnm, lnm         inserted      insert employee(eid, enm)        select             eid, enm         inserted end 

the trigger #2 (same trigger #1, values inserted through parameters)

create trigger emptrgg on emp instead of insert begin      declare @id int, @fname nvarchar(25), @lname nvarchar(25), @pid int, @emnum nvarchar(15)       select @id = eid, @fname =fnm, @lname = lnm      inserted       insert person(id, fnm, lnm)      values(@id, @fname, @lname)       insert employee(eid, enm)      values(@pid, @enum)  end 

which of above efficient or performance good?

i aint able findout,could me this?

regards, chio.

your trigger #2 has major flaw in assume it'll called once per row - not case.

the trigger fire once per statement, if insert statements affects 25 rows, you'll trigger fired once, inserted pseudo table contain 25 rows.

which of 25 rows code select here??

select @id = eid, @fname = fnm, @lname = lnm  inserted 

it's non-deterministic - you'll 1 arbitrary row , ignore 24 others.

use trigger #1 only! uses proper, set-based approach handle multiple inserted rows @ once. , since it's set-based, it's pretty optimal performance.