CODEWITHSUNDEEP
sqlsql-serverdatabase
XardasLord
XardasLord

Reputation: 1947

Procedure which modify existing trigger

I want to create procedure which modify existing trigger. Trigger is responsible for blocking rows from beeing updated with specific ID. I tried something like that:

CREATE PROCEDURE Change_trigger
    @List_of_ids varchar(8000)
AS 
ALTER TRIGGER blocks
ON ttt
INSTEAD OF update 
AS
BEGIN
    If (SELECT Id_ttt FROM inserted) IN (@List_of_ids)
      BEGIN
          raiserror('You cannot modify this record.', 12, 1)
          RETURN
      END

    UPDATE ttt
    SET
    field1 = INSERTED.field1
    FROM INSERTED
    WHERE INSERTED.Id_ttt = ttt.Id_ttt
END

Parameter @List_of_ids would be like this: 2,3,4,5,9,52. But when I try to create this procedure I got error:

Msg 156, Level 15, State 1, Procedure Change_trigger, Line 4
Incorrect syntax near the keyword 'TRIGGER'.

The trigger is created.

Upvotes: 1

Views: 65

Answers (2)

Shiju Shaji
Shiju Shaji

Reputation: 1730

Try this..

CREATE PROCEDURE Change_trigger
    @List_of_ids varchar(4000)
AS 
begin
declare @sql varchar(8000)

set @sql ='
ALTER TRIGGER blocks
ON ttt
INSTEAD OF update 
AS
BEGIN
    if exists (SELECT Id_ttt FROM inserted where  Id_ttt IN ('+@List_of_ids+'))
      BEGIN
          raiserror(''You cannot modify this record.'', 12, 1)
          RETURN
      END

    UPDATE ttt
    SET
    field1 = INSERTED.field1
    FROM INSERTED
    WHERE INSERTED.Id_ttt = ttt.Id_ttt
END' ;
exec (@sql);
END

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

This is the trigger I'd write, once.

ALTER TRIGGER blocks
ON ttt
INSTEAD OF update 
AS
BEGIN
    SET NOCOUNT ON

    UPDATE t
    SET
    field1 = i.field1
    FROM INSERTED i
            inner join
         ttt t
            on i.Id_ttt = t.Id_ttt
            left join
         ttt_blocked on tb
            on
               i.Id_ttt = tb.Id_ttt
    WHERE
        tb.Id_ttt is null
END

Note that this trigger no longer throws an error for blocked updates but it does allow for a mixed update (some rows blocked, some rows not) to occur. There's no clean way to raise an error whilst still partially applying an update in a trigger.

Then I'd have a table (referenced above):

CREATE TABLE ttt_blocked (
   Id_ttt int not null,
   constraint PK_ttt_blocked PRIMARY KEY (Id_ttt)
)

And then, if necessary, I'd create a procedure to maintain this table rather than continually changing the database schema:

CREATE PROCEDURE Change_blocking
  @BlockedIDs xml
AS
    --Better option would be table-valued parameters
    --but I've chosen to do XML today

    --We expect the XML to be of the form
    --<blocks>
    --  <id>10</id>
    --  <id>15</id>
    --</blocks>

    MERGE INTO ttt_blocked t
    USING (select x.id.value('text()[1]','int')
           from @BlockedIDs.nodes('/blocks/id') x(id)) s(Id_ttt)
    ON
      t.Id_ttt = s.Id_ttt
    WHEN NOT MATCHED THEN INSERT (Id_ttt) VALUES (s.Id_ttt)
    WHEN NOT MATCHED BY SOURCE THEN DELETE;

As I also allude to above, I'd generally recommend Table-Valued Parameters rather than XML (and either of them ahead of varchar since they're designed to hold multiple values) but it would have added even more code to this answer.

Upvotes: 1

Related Questions