Audit table drops with recyclebin hooks

🧪

Hands-on lab

Run this module yourself — the `before/` source, the three-engine sandbox, and the worked solution to diff against.

Demos/Learn/course4-recipe-08 →

The table’s in the recyclebin. The data’s safe. Good — but who dropped it? When? How many rows went with it? The simple soft-drop saves the table and tells you nothing, and six months later when someone asks “what happened to Coupon,” you’re reading deploy logs and guessing.

Last recipe we installed the simplest hook there is: rename the table aside, rename it back. That’s the whole soft-drop. But that hook is a procedure — and the procedure is yours. So let’s author one that does more than move a table quietly: one that keeps a record.

The lever: the contract is fixed, the body is yours

SchemaQuench’s side of the deal never changes. When a table leaves the product, it calls CustomTableDrop instead of running a hard DROP. When a table’s about to be created, it calls CustomTableRestore first. Detect the hooks by name, route through them, done. What happens inside those procs is entirely your call.

So we write a richer body. It has an audit job and a correctness job.

The audit job — the part that answers “what happened to Coupon”:

• Count the rows before the table moves, so the record says how much rode into the archive.
• Archive under a timestamped name — Coupon__dropped_20260704215904493, not a fixed alias. Every drop is its own record; repeats never collide.
• Write an audit row — schema, table, archived name, row count, retention, and who ran it — into a TableDropAudit table that doubles as the restore registry.

The correctness job — the part that keeps the archive from breaking the next deploy. A renamed table drags its baggage with it, and two pieces of that baggage bite:

• Strip the table’s own constraints first. A primary key, unique, check, or foreign-key constraint has a schema-scoped name. Leave PK_Coupon on the archived copy and the next CREATE TABLE dbo.Coupon fails — the name’s already taken. So drop them before archiving; the engine’s “Add Missing…” passes re-add them from the model when the table is restored. (SchemaQuench already clears inbound foreign keys before it calls you — you handle the table’s own.)
• Clear the ownership marker. SchemaSmith tags every object it manages — an extended property on SQL Server, an ownership row on PostgreSQL and MySQL. If the archived copy keeps that tag, the next quench sees a table it owns that isn’t in the product, decides it was removed, and routes it right back through your drop hook — every run. Strip the tag and the archive drops off the radar.
• Protect the registry itself. Your TableDropAudit table is a table like any other: declared in the package, owned by SchemaSmith, dropped by absence. Stop declaring it one day and the engine does exactly what you taught it to — hands it to your own hook, which archives your bookkeeping under a timestamp and takes the restore trail with it. Mark it "PreventDrop": true in its table JSON. That protection is sticky: it survives the table leaving the package, which is the whole point. Set it while the table’s still declared — the marker’s refreshed from the package on every run.

We shipped that last one wrong in our own demo recyclebin. The hook ate its own registry. A drop hook is the one procedure in your package that can consume the thing it reports to, so give it the one table it isn’t allowed to touch.

Here’s the SQL Server drop hook with both jobs. Note the signature — the full documented one:

CREATE OR ALTER PROCEDURE SchemaSmith.CustomTableDrop
    @SchemaName SYSNAME, @TableName SYSNAME, @RetentionDays INT = 90
AS
BEGIN
    DECLARE @src NVARCHAR(300) = QUOTENAME(@SchemaName)+'.'+QUOTENAME(@TableName);
    IF OBJECT_ID(@src) IS NULL RETURN;                                   -- already gone: no-op
    DECLARE @rows BIGINT, @cntSql NVARCHAR(400) = N'SELECT @c = COUNT_BIG(*) FROM ' + @src;
    EXEC sp_executesql @cntSql, N'@c BIGINT OUTPUT', @c = @rows OUTPUT;

    -- correctness: free the schema-scoped constraint names (FKs first), then clear ownership
    DECLARE @drop NVARCHAR(MAX) = N'';
    SELECT @drop = @drop + N'ALTER TABLE '+@src+N' DROP CONSTRAINT '+QUOTENAME(name)+N';'
      FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(@src);
    SELECT @drop = @drop + N'ALTER TABLE '+@src+N' DROP CONSTRAINT '+QUOTENAME(name)+N';'
      FROM sys.objects WHERE parent_object_id = OBJECT_ID(@src) AND type IN ('C','D','UQ','PK');
    IF @drop <> N'' EXEC(@drop);
    IF EXISTS (SELECT 1 FROM sys.extended_properties
               WHERE major_id = OBJECT_ID(@src) AND minor_id = 0 AND name = 'ProductName')
        EXEC sys.sp_dropextendedproperty @name=N'ProductName',
             @level0type=N'SCHEMA', @level0name=@SchemaName, @level1type=N'TABLE', @level1name=@TableName;

    -- audit: timestamped archive + a full record
    DECLARE @archived SYSNAME = @TableName + N'__dropped_' + FORMAT(SYSUTCDATETIME(),'yyyyMMddHHmmssfff');
    EXEC sp_rename @src, @archived;
    INSERT INTO SchemaSmith.TableDropAudit (SchemaName, TableName, ArchivedName, RowsArchived, RetentionDays, Action)
    VALUES (@SchemaName, @TableName, @archived, @rows, @RetentionDays, 'DROP');
END

The engine only ever passes it the schema and table — @RetentionDays defaults at the parameter level, so the proc still binds to the engine’s two-argument call. That default is why you can author a three-parameter hook the two-argument caller still reaches. The PostgreSQL and MySQL hooks do the same two jobs; only the catalog you read and the “clear ownership” step differ — Postgres and MySQL keep an ownership row to delete instead of an extended property to drop.

The recipe: a drop you can answer for

Install the hooks, deploy a package with a Coupon table, put two rows in it. Now deploy the package that no longer defines Coupon. The drop hook fires — and instead of a silent rename, it leaves a record:

TableName  ArchivedName                        RowsArchived  RetentionDays  Action
Coupon     Coupon__dropped_20260704215904493   2             90             DROP

The row count was captured before the table moved, and the retention window’s on the record. The archived copy is left bare — its constraints stripped, its ownership tag cleared — so it can’t collide with a future Coupon or get mistaken for a table to re-drop on the next run. And when Coupon returns to the product, the restore hook looks up the most recent archived copy, renames it back before the engine would recreate it, and closes the loop:

Action    ArchivedName
DROP      Coupon__dropped_20260704215904493
RESTORE   Coupon__dropped_20260704215904493

The two rows came back intact, and the whole round-trip — who, when, how many — is sitting in one table you can query. Same behavior on SQL Server, PostgreSQL, MySQL, and MariaDB; only the hook names, the rename syntax, and the “who am I” function differ.

The aha: a soft-drop is an event worth recording

Notice how much of that hook was correctness, not flavor — stripping constraints so schema-scoped names stay free, clearing the ownership tag so the archive doesn’t boomerang back through the hook. That’s not incidental; it’s exactly why the reference recyclebin that ships with the Northwind demos is as involved as it is — a recyclebin schema, constraint stripping, retention and expiration, a scheduled cleanup job. Reach for it when it fits. But the reason it can fit so many shops is the same reason you can write your own: the hook is just a procedure at a known name. Your compliance team wants the dropper’s login on every archived table? Add a column. Need the row count for a capacity report? It’s already captured. Want every historical copy kept, not just the latest? The timestamped name does that for free. You’re not choosing from a menu of recyclebin features — you’re authoring the behavior, and the engine calls it exactly where a hard DROP or CREATE would have run. Author it, and own what it does — baggage and all.

Check yourself: The recyclebin hooks are just procedures at known names. What does that let you do beyond the simple rename-aside soft-drop?

Author the body yourself. Because SchemaQuench only cares that CustomTableDrop / CustomTableRestore exist by name, the logic inside is yours — capture a row count, archive under a timestamped name, and write a full audit row (who, when, how many, retention) to a table that doubles as the restore registry. But owning the body means owning its correctness too: strip the table’s own schema-scoped constraints so a future create can’t collide, and clear the ownership tag so the archive isn’t re-detected and re-dropped every run. The contract is fixed; the behavior — and the responsibility — is yours.

---

Every smith keeps a mark, and the good ones keep a ledger too — not just what left the fire, but when, and how much metal was in it. These hooks are that ledger. The simple soft-drop sets a piece aside; this one sets it aside and writes down the whole of it, so nothing leaves the forge unaccounted for and nothing comes back a mystery.

Got a soft-drop that needs to answer to an auditor — a table you can’t just quietly move, but have to account for? Email me at forgebarrett@schemasmith.com — I read every one. More’s coming from the forge.

Until then, may every drop you catch leave a clean record, and nothing you set aside slip away unmarked.

— Forge

Mark this lesson complete