Home / Questions / 208327
Accepted
Wilmilcard
Asked: 2020-10-27 11:28:26 +0800 CST 2020-10-27 11:28:26 +0800 CST

Make a Trigger updating a field with UPDATE and with DELETE

  • 772

I have a table called City:

CREATE TABLE dbo.Ciudad(
    Id          varchar(36) NOT NULL,
    DptId       varchar(36) NOT NULL,
    Nombre      varchar(40) NOT NULL,
    Version     Timestamp NULL,
    CreatedAt   datetimeoffset NULL DEFAULT getdate(),
    UpdatedAt   datetimeoffset NULL,
    Deleted     bit NOT NUll DEFAULT(0),
    CONSTRAINT [PK_Ciudad] PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT [FK_Ciudad_DptId] FOREIGN KEY (DptId) REFERENCES dbo.Dpto(Id) 
)

The field UpdatedAthas to be updated every time an UPDATE is done on the record. How can I do it with a Trigger?

At the moment I only have this:

UPDATE:

CREATE TRIGGER dbo.tr_Ciudad_Update ON dbo.Ciudad AFTER UPDATE
AS
DECLARE @id as VARCHAR
BEGIN
    RAISERROR(@id ,16,-1)
    UPDATE Ciudad SET UpdatedAt = getdate() WHERE Id = @id; 
END

DELETE

CREATE TRIGGER dbo.tr_Ciudad_Eliminar ON dbo.Ciudad INSTEAD OF DELETE
AS 
DECLARE @id as VARCHAR
BEGIN
    UPDATE Ciudad SET UpdatedAt = getdate(), Deleted = 1 WHERE Id = @id; 
    RAISERROR('CAMPO DELETED ACTUALIZADO A TRUE',16,-1)
    ROLLBACK TRANSACTION
END

The idea is that the delete does not delete the record but it updates the field Deletedbut the Triggers do not work for me

sql

1 Answers

  1. Best Answer

    I already solved it, so I leave it:

    UPDATE

    IF EXISTS (SELECT * FROM sys.objects WHERE name='tr_Ciudad_Update' AND type='TR')
   DROP TRIGGER dbo.tr_Ciudad_Update

execute dbo.sp_executesql @statement = N'
CREATE TRIGGER dbo.tr_Ciudad_Update ON dbo.Ciudad AFTER UPDATE
AS
DECLARE @id as VARCHAR(36)
SELECT @id = id FROM deleted
BEGIN
    UPDATE Ciudad SET UpdatedAt = getdate() WHERE Id = @id 
END
'

    DELETE

    IF EXISTS (SELECT * FROM sys.objects WHERE name='tr_Ciudad_Eliminar' AND type='TR')
   DROP TRIGGER dbo.tr_Ciudad_Eliminar

execute dbo.sp_executesql @statement = N'
CREATE TRIGGER dbo.tr_Ciudad_Eliminar ON dbo.Ciudad INSTEAD OF DELETE
AS 
DECLARE @id as VARCHAR(36)
SELECT @id = id FROM deleted
BEGIN
    IF ((SELECT Deleted FROM Ciudad WHERE id = @id) = 0)
        BEGIN
            ROLLBACK TRANSACTION
            UPDATE Ciudad SET Deleted = 1 WHERE Id = @id;
        END
    ELSE
        print ''El campo ya esta eliminado''
END
'
    • 0