Do a Trigger updating a field with UPDATE and with DELETE

Question

Do a Trigger updating a field with UPDATE and with DELETE

Navigation

#1 by (0 votes)

1

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 UpdatedAt field must be updated each time an UPDATE is made in the registry. 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 erase the registry but I update the Deleted field but the Triggers do not work for me

sql trigger update

asked by Wilmilcard 26.10.2018 в 21:28

source

1 answer

the jnlp files can not be executed in openJDK11 How to pass parameters of the view to the Controller in ASP.NET MVC with httpPost

score 0 · Accepted Answer

I already solved it, so I left 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
'