Creating triggers and Stored proceedures

This doesn't give me any result :( I want to create a trigger to prevent deletion of the primary key first

Try like this

ALTER TRIGGER trg_AvoidPKDeletion
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
DECLARE @SQLCommand varchar(max)= EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
IF  EXISTS (select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
     , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
     , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
  from sys.key_constraints as k
  join sys.tables as t
    on t.object_id = k.parent_object_id
  join sys.schemas as s
    on s.schema_id = t.schema_id
  join sys.index_columns as ic
    on ic.object_id = t.object_id
   and ic.index_id = k.unique_index_id
  join sys.columns as c
    on c.object_id = t.object_id
   and c.column_id = ic.column_id
   where  k.type_desc = 'PRIMARY_KEY_CONSTRAINT'
   AND @SQLCommand =  'ALTER TABLE ' + s.name + ' DROP CONSTRAINT ' + k.name  )
   
   PRINT 'PK constraints cannot be dropped'
   ROLLBACK
  END

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://ift.tt/19nLNVq http://ift.tt/1iEAj0c