Stored procedure inside a trigger for logging

Stored procedure inside a trigger for logging

admin

I am using an identical trigger for multiple tables for logging DML events and because of that I have lots of redundant code. How can I write a stored procedure and call it inside those triggers to log data?

Here is what my trigger looks like

CREATE OR ALTER TRIGGER [Person].tr_logInsertDeleteOrUpdateemployee
ON [Person].employee
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @tableName varchar(100) = '[Person].employee'

    IF EXISTS (SELECT TOP 1 * FROM inserted) 
       AND EXISTS (SELECT TOP 1 * FROM Deleted)
    BEGIN
        INSERT INTO dbo.DMLLogs
            SELECT updatedRecord = 'updated row', @tableName, ID, SYSTEM_USER, GETDATE()  
            FROM deleted
    END

    IF EXISTS (SELECT TOP 1 * FROM inserted) 
       AND NOT EXISTS (SELECT TOP 1 * FROM Deleted)
    BEGIN
        INSERT INTO dbo.DMLLogs
            SELECT insertedRecord = 'inserted', @tableName, ID, SYSTEM_USER, GETDATE() 
            FROM inserted
    END

    IF EXISTS (SELECT TOP 1 * FROM deleted) 
       AND NOT EXISTS (SELECT TOP 1 * FROM inserted)
    BEGIN
        INSERT INTO dbo.DMLLogs
            SELECT deletedRecord = 'deleted from', @tableName, ID, SYSTEM_USER, GETDATE() 
            FROM deleted
    END
END
more