CODEWITHSUNDEEP
sql-servert-sqlddl-trigger
George Menoutis
George Menoutis

Reputation: 7240

DDL trigger to prevent dropping PK

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>

Upvotes: 0

Views: 100

Answers (1)

Charlieface
Charlieface

Reputation: 71203

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...

Upvotes: 1

Related Questions