Tables With Triggers

Overview

The Tables With Triggers report answers what fires when something writes to this table – which is a far more useful question than how many triggers are there.

“How many” is the least interesting fact about a trigger. What matters is which events it fires on, whether it runs instead of the write or after it, whether it is switched on, how much code is in it, and what it writes to in turn.

The main view is a firing matrix: one row per table, one cell per event and timing combination, so AFTER INSERT and INSTEAD OF DELETE are different cells rather than different rows. A marginal histogram above the columns shows where the triggers cluster across the database, and clicking a column filters the page to it.

The second view draws the cascade chains as a node-link diagram – a write to one table firing a trigger that writes to another, which fires another. That chain is invisible in any list and is where trigger surprises live.

Nothing on this page runs anything. Enabling or disabling somebody’s business logic is not a click, so the right-click menu scripts those statements to the clipboard and stops.

What this fixes

The old report was a bar chart of trigger counts over a four-column grid, and it was wrong in ways that hid real findings:

• The bar was a trigger count, which is one, two or three. Every bar was one of three lengths, so the chart carried no information at all – and its colours came from the row index, so a hue meant nothing and changed whenever a row moved.
• sys.triggers holds DML triggers on views as well as tables, and database-level DDL triggers. The old query left-joined sys.tables, so every view trigger and every DDL trigger came back with a null schema and table, and the grouping merged the whole lot into one blank row with a count on it. On a database with an INSTEAD OF view trigger and an audit DDL trigger, that blank row was the top of the report.
• WHERE is_disabled = 0 threw away the most important finding on the page. A disabled trigger is business logic somebody switched off, usually mid-incident, often years ago, and nothing else in the product looks for one.

---

Where to find it

This is a database-level report. Select a database in the tree, then open Tables With Triggers.

The page title reads Tables With Triggers for <database name>.

---

Reading the matrix

One row per table, one cell per event-and-timing combination. The bar is lines of trigger code, not a trigger count – a thousand-line trigger and a three-line one are different risks and now look different.

The chart draws the busiest 14 tables; the grid carries every trigger.

The marginal histogram

Above the columns, a histogram shows how many triggers fire on each event across the whole database. Clicking a column filters the page to it – the fastest way to answer “what fires on delete anywhere in this database”.

The chips

Chips name what is worth knowing about a table’s triggers. Everything on a chip is also in the tooltip and the grid’s Notes column, so nothing is only on a chip.

Chip	Meaning
2 disabled (red)	Business logic that is switched off.
View (blue)	The trigger is on a view, not a table.
Chains to 3 (blue)	This table’s triggers write to three other tables.
4 on INSERT (amber)	Several triggers on one event – firing order matters and is rarely defined.
Cursor (amber)	A trigger contains a cursor, which runs per row inside a write.
Assumes 1 row (amber)	A trigger written as if inserted holds one row. It does not, on a multi-row write.
No NOCOUNT (amber)	Missing SET NOCOUNT ON, which sends extra row-count messages to the client.
Encrypted (outlined)	The body cannot be read.
Not for replication (outlined)	Context.

Assumes 1 row is the one worth hunting. A trigger that treats inserted as a single row works perfectly until somebody writes two, and then it silently corrupts data.

---

The Cascades view

A node-link diagram of the write chains: table → trigger → table. This is the view that answers “what actually happens when I update this row”, which no list can.

A long chain is a design worth knowing about before you debug it. A cycle is worth knowing about immediately.

---

Reading the grid

One row per trigger, not per table.

Column	What it is
Table	The table or view the trigger is on.
Trigger	Trigger name.
Fires On	The events – insert, update, delete, or a combination.
Timing	AFTER or INSTEAD OF.
Lines	Lines of trigger code.
Table Rows	Row count of the table it is on – the same trigger is a different risk on a table with ten rows and one with ten million.
Modified	When it was last changed.
Status	Enabled or Disabled.
Notes	Everything the chips say, in words.

---

The toolbar

Group	Buttons
Filter	All · Disabled · Cascading · Caution · Instead of
View	Matrix · Cascades
Rank by	Code size · Triggers · Table rows
	Refresh

Rank by is the control that changes the question. Code size finds the biggest bodies, Triggers finds the most crowded tables, Table rows finds the triggers running on the most data.

---

Right-click actions

Item	What it does
Copy CREATE TRIGGER script	The full body.
Copy ENABLE TRIGGER script / Copy DISABLE TRIGGER script	Whichever applies. Clipboard only – the report never runs these.
Copy sp_settriggerorder script	Offered when firing order matters, which is when a table has several triggers on one event.
Copy what-fires-on-this-table script	An investigation query.
Copy trigger name	Just the name.

Double-clicking a row opens the trigger body in a viewer. If some triggers on the table are encrypted, the viewer says so rather than silently showing fewer.

---

How to read the report

1. Filter to Disabled first. Switched-off business logic is the finding nothing else in the product looks for.
2. Look for Assumes 1 row chips. These are latent data-corruption bugs waiting for a multi-row write.
3. Check the INSTEAD OF filter. An INSTEAD OF trigger means the write you think is happening may not be.
4. Switch to Cascades. Chains and cycles are invisible in the grid.
5. Rank by Code size. A very large trigger body running inside every write is a performance problem as well as a maintenance one.
6. Look at Table Rows next to Lines. Heavy trigger code on a large table is where the cost is.
7. Check for crowded cells. Several triggers on one event fire in an undefined order unless sp_settriggerorder has been used.

Common patterns

A disabled trigger nobody remembers. Switched off during an incident and never switched back. Either the logic is needed or it is not; leaving it disabled is a decision nobody made.

A trigger with a cursor. It runs per row, inside a write, holding locks. Almost always rewritable as set-based logic.

Several triggers on INSERT for one table. They fire in an order SQL Server does not guarantee unless somebody set it. The menu offers the sp_settriggerorder script for exactly this.

A long cascade chain. One write firing a chain of trigger-driven writes. Worth documenting even when it is correct, because the next person to debug it will not expect it.

INSTEAD OF triggers on views. Often legitimate, and invisible on the old report because view triggers collapsed into a blank row.

No NOCOUNT everywhere. Minor individually, but a trigger without SET NOCOUNT ON sends extra row-count messages to the client on every write, and some client libraries handle them badly.

---

Where the data comes from

sys.triggers, joined properly to both tables and views, plus sys.sql_modules for the bodies and row counts for context. DDL triggers are recognised rather than merged into a blank row.

---

Related reports

Report	Why you would go there
Stored Procedures	The other place business logic hides.
Deadlocks by Database	Triggers extend the time a write holds locks.
Statistics	Triggers writing to other tables change what those tables’ statistics describe.

---

Frequently asked questions

Why are disabled triggers shown when the old report hid them? Because a disabled trigger is the single most interesting thing on this page. It is business logic somebody switched off.

Why is the bar lines of code rather than a trigger count? Because a count is one, two or three, and a chart of three possible lengths carries no information. Code size varies by orders of magnitude and correlates with both risk and cost.

What does Assumes 1 row mean? The trigger body treats the inserted or deleted pseudo-table as if it holds a single row. That works until somebody writes two rows in one statement.

Why can I not enable or disable a trigger from here? Because turning somebody’s business logic on or off is not a click. You get the script.

Why do view triggers appear? Because they exist and they fire. The old query merged them into an unlabelled blank row.

What is the Cascades view for? Seeing what a single write actually sets off. A chain of trigger-driven writes is invisible in a list.

---