You start a subquery on line 6, but you never end it with a bracket nor alias the subquery. That should go between rows 10 and 11.
You'll also need...

I'm not a site moderator.
And I know Sue pointed it out, that's why my post was a reply to Sue

Your workarounds are unfortunately useless if you can't restrict permissions. Someone who wants to drop a DB can disable the trigger, drop the DB, enable the trigger and there's no...

You should mention in that script that it's index scans since the last time the database or SQL Server were restarted. Not over all time.

Change it via the machine's Control Panel

There's a subquery...

Column aliases are applied after the rest of the statement has been evaluated. Hence you can't define and use a column alias in the same level of scope.

Put...

The two different promotion tables are a concern in terms of DB design. You can also combine two of those IF statements into one, as what they do is identical.

Details?
Migrating to a new version? A different server? The cloud? A different database engine?

Few things.
If a full log caused a suspect DB, then you've hit a bug where there was insufficient log reservation. Please report that to Microsoft with a repo if...