Can anyone help me out how to perform MySQL trigger logic from existing MS SQL trigger Logic-CodePudding

I have two tables one is Master table where the Daily data keeps loading. Other one is Audit Table which tracks whenever there is an update (single/multiple columns updates) happened on Master Table. Below is the logic used for MS SQL

/*** Mastertable creation **/
CREATE TABLE [dbo].[Master](
    [ID] [uniqueidentifier] primary key NOT NULL DEFAULT (newid()),
    [Name] [varchar](100) ,
    [Status] [varchar](10),
    [Dept] [varchar](10) ) 

/** Inserting Data to Master table ***/
Insert into [dbo].[Master] ([Name],[Status],[Dept]) Values
('AAAA', 'Open','EC'),
('BBBB', 'Closed','CS') 

/** Audit Table creation ***/
CREATE TABLE [dbo].[Orders_Audit](
    [Id] [uniqueidentifier] NOT NULL,
    [ColName] [varchar](50),
    [OldValue] [varchar](200),
    [NewValue] [varchar](200),
    [ModifiedAt] [datetime],
    [ModifiedBy] [varchar](50) ) 
Go

/*** Trigger used based on columns for any updates in Master Table***/

Create TRIGGER [dbo].[Tr_Master]
ON [dbo].[Master]
FOR UPDATE 
AS

BEGIN

        Declare @action varchar(50)

    IF UPDATE([Status])
           BEGIN
                  SET @Action = 'Status'

    Insert into [dbo].[Orders_Audit] (Id,ColName,OldValue,NewValue,ModifiedAt,ModifiedBy)
    select i.Id,@action ,d.[Status] ,i.[Status] ,getdate() ,SUSER_SNAME()
    from inserted i,
    deleted d where i.Id = d.Id
    END


    IF UPDATE([Dept])
           BEGIN
                  SET @Action = 'Dept'

    Insert into [dbo].[Orders_Audit] (Id,ColName,OldValue,NewValue,ModifiedAt,ModifiedBy)
    select i.Id,@action ,d.[Dept] ,i.[Dept] ,getdate() ,SUSER_SNAME()
    from inserted i,
    deleted d where i.Id = d.Id
    END

End

GO

Need to implement same logic in MySQL Thanks in Advance!!

CodePudding user response：

CREATE TRIGGER trigger_name 
AFTER UPDATE ON Master
FOR EACH ROW
BEGIN
IF NOT (OLD.Status <=> NEW.Status) THEN
    INSERT INTO Orders_Audit (Id, ColName, OldValue, NewValue, ModifiedAt, ModifiedBy)
    SELECT NEW.Id, 'Status', OLD.Status, NEW.Status, NOW(), CURRENT_USER();
END IF;
IF NOT (OLD.Dept <=> NEW.Dept) THEN
    INSERT INTO Orders_Audit (Id, ColName, OldValue, NewValue, ModifiedAt, ModifiedBy)
    SELECT NEW.Id, 'Dept', OLD.Dept, NEW.Dept, NOW(), CURRENT_USER();
END IF;
END

IF NOT (OLD.column <=> NEW.column) checks that the column value was changed. This expression is NULL-safe (rather than IF OLD.column <> NEW.column) - i.e. it correctly checks the result when one of the values or both of them are NULL.

PS. CURRENT_USER() returns the account name which was used for authentication. If you need the username provided by the client during authentication then use USER() function instead. Example: user may provide 'john'@'1.2.3.4' (and USER() returns this) but the account used may be 'john'@'%' (and CURRENT_USER() returns this). For stored objects/views CURRENT_USER() may return not invoker but definer account (depends on SECURITY attribute).