Rollback transaction from trigger
sql, sql-server-2008, sql-server-2008-r2
Solution
Yes.
You do need to write the explicit `INSERT` or `UPDATE`.
The trigger runs `INSTEAD OF` the DML operation. If you leave the trigger blank then no action will happen other than the `INSERTED` / `DELETED` tables being created and populated in `tempdb`.
Although from discussion in the comments I would not use a trigger for this at all but use a unique filtered index `CREATE UNIQUE INDEX ix ON T(a,b,c) WHERE c <> ''`. This is likely to be more performant and avoid potential logic issues when dealing with concurrency.
Problem
In MS SQL Server 2008 R2, we want a pre-insert and pre-update trigger which checks something and allows or rollbacks (via `raiserror`) the running insert/update. Question: In `INSTEAD OF` trigger. Does one really has to explicitly write the insert or update? Because we want the default insert or update to be done and only do the "precheck".