DDL trigger to prevent dropping PK

Question

I have a table with a PK

create table a(x int constraint PK_x primary key(x))

How do I make a DDL trigger that prevents the dropping of the PK?

alter table a drop constraint PK_x

I can't get if the constraint is a PK from EVENTDATA

<EVENT_INSTANCE>
  <EventType>ALTER_TABLE</EventType>
  <PostTime>2021-11-04T13:08:11.417</PostTime>
  <SPID>73</SPID>
  <ServerName>SQL2017</ServerName>
  <LoginName>sa</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>(redacted)</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>a</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <AlterTableActionList>
    <Drop>
      <Constraints>
        <Name>PK_x</Name>
      </Constraints>
    </Drop>
  </AlterTableActionList>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>alter table a drop constraint PK_x</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver15 — Mitch Wheat, Nov 04 '21 at 11:11
"prevent dropping PK" - the fact this is a problem leads me to suspect its's another XY problem! In 25+ years , I have never had to worry about a PK being dropped. — Mitch Wheat, Nov 04 '21 at 11:12
@Larnu The PK is already removed from the database while in the trigger. That's why I mentioned the rollback in that other question - it was so that I could `then` look at sys.objects to see if that was a PK. — George Menoutis, Nov 04 '21 at 11:14
I agree, @MitchWheat . If you are attempting to `DROP` a `PRIMARY KEY` it is by definition not being used by any other tables as a `FOREIGN KEY`. Though there are going to be some `CONSTRAINT`s that won't be. So the majority of `DROP` statements should be blocked anyway one would hope; unless there is a significant lack of referential integrity in the database. — Thom A, Nov 04 '21 at 11:14
So you recommend just to add FK to the other table. Makes sense I guess. — George Menoutis, Nov 04 '21 at 11:16
...and in addition, use permissions to prevent logins doing operations that don't make sense. — Mitch Wheat, Nov 04 '21 at 11:16
@Mitch Wheat You seem to think that I would make a question here without first thoroughly scouring ms documention, msdn forums, google and SO. I assure you, this is not the case. Let me see where exactly in your proposed doc page is the answer. — George Menoutis, Nov 04 '21 at 11:33
"just create a foreign key" seems like a huge kludge and another "mysterious" fact you must include in your system documentation, source control, and recovery plan doc (does it exist?). A foreign key is as easy to drop as a primary key so how safe is that? Address the real problem - who is dropping primary keys and why are they doing so? Maybe someone should not have permissions to do that - or needs more training / supervision? — SMor, Nov 04 '21 at 11:51

Charlieface · Answer 1 · 2021-11-04T15:19:07.720

1

You can use XQuery to get the Primary Key Constraint name, and check it against a list of constraints

CREATE OR ALTER TRIGGER ddl_trig_PrimaryKeys
ON DATABASE
FOR ALTER_TABLE
AS

DECLARE @PKname sysname = EVENTDATA().value('
  (/EVENT_INSTANCE[ObjectType[text() = "TABLE"]][ObjectName[text() = "a"]]
   /AlterTableActionList/Drop/Constraints/Name
   /text()[. = "PK_x" or . = "PK_y"])[1]',
  'nvarchar(max)');

IF @PKname IS NOT NULL
BEGIN
    THROW 50000, 'Dropping Primary Key constraint has been forbidden by trigger', 0;
END;

GO

Whether this is altogether a good idea to implement, I will leave to you...

edited Nov 04 '21 at 15:19

answered Nov 04 '21 at 12:38

Charlieface

52,284
6
19
43

Thanks for understanding. However, this doesn't work, since by the time the trigger runs, it is already removed from sys.objects . – George Menoutis Nov 04 '21 at 12:42
Fair enough, but we don't really need that check anyway. We have the name, you can just check it against a list – Charlieface Nov 04 '21 at 12:43

DDL trigger to prevent dropping PK

1 Answers1