SQL Server 2012 using SELECT in trigger breaks table -

so let me first admit sql server newbie.

here's deal: i'm trying create trigger on table in sql server 2012, , whenever try kind of select statement in trigger, table quits working (as in nothing can inserted until trigger deleted). drop trigger, starts working again. if don't selects, peachy. there permission or somewhere i'm missing?

example:

create trigger sometrigger on sometable insert begin     select * inserted end go 

command completes successfully, table becomes frozen described above.

create trigger sometrigger on sometable insert begin     exec msdb.dbo.sp_send_dbmail     @recipients = n'someaddress@somedomain.com',     @subject = 'test',     @body = 'test body',     @profile_name = 'someprofile' end go 

works charm.

you're may falling foul of disallow results triggers option being set 1, should be.

note warning on page:

this feature removed in next version of microsoft sql server. not use feature in new development work, , modify applications use feature possible. recommend set value 1.

i suspect wherever you're running inserts hiding error message or exception, since should get:

msg 524, level 16, state 1, procedure , line

"a trigger returned resultset , server option 'disallow_results_from_triggers' true."

---

or, in alternative, you're working database layer wraps inserts in transaction , roll transaction if unexpected happens - such receiving result set or information message saying (x rows affected).

but of dancing around main issue - shouldn't issuing select attempts return results inside of trigger. might have been able offer more if you'd told you're trying achieve.

if it's second case, , it's tripping on (x rows affected) messages, can cured placing set nocount on @ top of trigger.