Why triggers are bad

SQL Profiler is recommended to run in another Server. In other words, it is not recommended to replace triggers with SQL Profiler. The best alternative to Profiler is extended events. DDL triggers are used to execute an action for events like creating a new database, altering a table, granting permissions. In this example, we will create a table in the master database to store the execution time, SQL User, Event and query executed. This table will store that information when a database is created using triggers.

An alternative to triggers is Extended Events. Creating a new session. Specify a session name and check the Start the event session immediately after session created and Watch live data on screen as it is captured option:. Session name and schedule properties. Watching the data. The data captured.

Extended Events is a great alternative to triggers. It is simple and it does not consume as much resources like SQL Profiler. There are some DBAs that say that we should never use triggers, but if it does not affect the performance of your code, if they are not used all the time, it is not bad to use it in your code.

Make sure to disable for massive bulks and be careful with recursive triggers. Triggers are not evil, but they must be used wisely.

ON dbo. USE master. Create database test4. Author Recent Posts. Daniel Calbimonte. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams. Latest posts by Daniel Calbimonte see all.

People complain about nested triggers, about triggers that are overly complex, about triggers which do too many things… but stored procedures have all these faults too. It's just that you knew that the stored procedure was there, and you didn't know that the trigger was there. They're code and should be treated as such.

In those two posts I linked to at the start, I show ways you can use triggers to effectively have table-valued parameters without user-defined table types. I think this is really handy and somewhat elegant, except that the code isn't obvious.

Of course you can document them, and find ways to let your DBAs know they're there… but still they will be considered semi-visible, and be nefarious by default. Like many features of technology, they will be used by well-meaning developers, and often the impact will be negative rather than positive. You may not be able to change the fact that they're used without refactoring major parts of the architecture again, like asbestos , but there are some things you can do:.

Be pragmatic. Accept that you will come across systems that use triggers. Don't overreact or ask that developers be fired — remember that you've seen worse. Document them. Make sure that you don't forget they're there, and that those that come after you can discover them easily. Learn to appreciate the power of them. I'm not saying you should start implementing them, unless you're pushing data around using SSIS and are wishing you had TVPs, but you could look at them as being like stored procedures and appreciate that they could be an elegant solution.

Tune them. You tune the stored procedures in your system, and you can tune the triggers too. Every bad thing you come across is an opportunity to demonstrate your strength as a DBA. For more on triggers, go and check out the other posts this month.

I'll leave it up to you to decide whether triggers are good or bad — I think it'll depend on how they've been done. I like triggers. Like any tool, it's a tool. That is not the only kind of data changes that happen to a database and all triggers should be tested under the conditions of 1 record change and many record changes.

Forgetting to do the second test can lead to extremely poorly performing triggers or a loss of data integrity. Another use case I've personally come across is for databases that are accessed by more than one program. If you want to implement functionality but not redesign all systems for it, a trigger makes a sensible solution.

For example, I recently worked on a database that had previously existed as solely an office system. When a webapp was written to interface with it, we wanted to implement a notification system similar to stackexchange, for example , which would be fired by multiple events, such as processing a transaction, and so forth. We were able to implement a trigger so that updates in the office backend fired a trigger to create the notification for the frontend and tell the user that their transaction had been processed by the office.

Lets say you need to push data to a 3rd party system in near real time. Your table contains gigabytes of data, so it's too big to just simply push the entire table to the 3rd party app. Instead you accumulate changes in a queue.

Some external program will then periodically push out small batches of queued data. The system has over stored procedures. You also know that tons of sql exists in the source code. To guarantee the queue is populated correctly you'll have to search through all the stored procs and code and hope you don't miss anything.

Instead you can put a trigger on the table to keep the queue updated. Guaranteed not to miss anything. One central location. Performance penalty? Not really because the hit of populating the queue cannot be avoided whether it's by trigger, or external. In this scenario I would say not using a trigger is a bad design choice. If later you want to use a new method of pushing data say the queue isn't working out and the interface changes you are protected if you use the trigger.

Triggers are often the best choice. Don't listen to dogmatic anti-trigger fanboys. Triggers can be used to enforce constraints on database which can't be enforced during database schema creation and any DML statements.

A trigger that sends email is not necessarily a 'stupid' idea. The 'stupid' part of this is really bad to non-existent error handling by lazy developers who feel they are immune to making mistakes.

That is why there are packages and libraries. Sign up to join this community. The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?

Learn more. SQL Triggers and when or when not to use them.