t-sql INSTEAD OF deleting a row PRINT its id-CodePudding

Is it possible to make such a trigger that runs when you try to delete a row and prints something like this: "Attempting to delete row ROW_ID", instead of actually deleting the row?

UPD: Thanks. Worked for me:

GO
CREATE OR ALTER TRIGGER Trigger_2 ON Authors
INSTEAD OF DELETE
AS BEGIN
    DECLARE @deleted_id INT;
    DECLARE cursor_deleted CURSOR
    FOR SELECT au_id FROM deleted;

    OPEN cursor_deleted;
    FETCH NEXT FROM cursor_deleted INTO @deleted_id;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT('Attempting to delete author '   STR(@deleted_id));
        FETCH NEXT FROM cursor_deleted INTO @deleted_id;
    END;

    CLOSE cursor_deleted;
    DEALLOCATE cursor_deleted;
END;

GO
DELETE FROM Authors WHERE au_id BETWEEN 1 AND 10;

CodePudding user response：

Yeah it is, and there are many approaches. using a trigger you can either set the trigger to run after a deletion and u will have to use "ROLLBACK" to undo the deletion operation. or just simply use INSTEAD OF where as the name suggests the trigger will replace the who deletion operation. hope this helps in any way. ps: after a deletion operation deleted data are stored in a Global table named "deleted" use to get any data or metaData that u need.

 CREATE TRIGGER schema_name.trigger_name
    ON table_name
     // this trigger is executed instead of any deletion
    INSTEAD OF DELETE
    AS
     BEGIN
       // return the once to be deleted's id. 
       return deleted.id
     END