Create a audit table from SQL TRIGGER

Transact-SQL
1

I would like to incorporate the results from this trigger into a audit table. please advise

USE msdb
GO
CREATE TRIGGER tr_SysSchedules_enabled
ON sysschedules
FOR UPDATE AS

SET NOCOUNT ON

DECLARE @username VARCHAR(50),
@hostname VARCHAR(50),
@schedulename VARCHAR(100),
@DeletedJobName VARCHAR(100),
@New_Enabled INT,
@Old_Enabled INT,
@bodytext VARCHAR(200),
@subjecttext VARCHAR(200),
@Servername VARCHAR(50)

SELECT @username = SYSTEM_USER, @hostname = HOST_NAME()
SELECT @New_Enabled = enabled FROM Inserted
SELECT @Old_Enabled = enabled FROM Deleted
SELECT @schedulename = name FROM Inserted
SELECT @Servername = @@servername

-- check if the enabled flag has been updated.
IF @New_Enabled <> @Old_Enabled
BEGIN

IF @New_Enabled = 1
BEGIN
SET @bodytext = 'User: '+@username+' from '+@hostname+
' ENABLED SQL Schedule ['+@schedulename+'] at '+CONVERT(VARCHAR(20),GETDATE(),100)
SET @subjecttext = @Servername+' : ['+@schedulename+
'] has been ENABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)
END

IF @New_Enabled = 0
BEGIN
SET @bodytext = 'User: '+@username+' from '+@hostname+
' DISABLED SQL Schedule ['+@schedulename+'] at '+CONVERT(VARCHAR(20),GETDATE(),100)
SET @subjecttext = @Servername+' : ['+@schedulename+
'] has been DISABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)
END

SET @subjecttext = 'SQL Schedule on ' + @subjecttext

-- send out alert email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourServerMailProfile',
@recipients = 'DBA_Notify@YourOrg.org',
@body = @bodytext,
@subject = @subjecttext

END

2

Don't know exactly what you want to INSERT, but triggers especially need to be written for efficiency. Therefore, don't declare unnecessary variables or do things in many separate statements.

I had no idea what actual data you want to insert to the audit table, but I put a fragment statement there.

USE msdb1
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER tr_SysSchedules_enabled
ON dbo.sysschedules
FOR UPDATE 
AS
SET NOCOUNT ON;

DECLARE @bodytext varchar(500)
DECLARE @subjecttext varchar(200)

;WITH cte_schedule AS 
(
    SELECT i.enabled AS new_enabled, d.enabled AS old_enabled,
        CAST(i.name AS varchar(100)) AS schedule_name,
        CAST(SYSTEM_USER AS varchar(50)) AS username,
        CAST(@@SERVERNAME AS varchar(50)) AS hostname
    FROM inserted i
    INNER JOIN deleted d ON d.schedule_id = i.schedule_id
    WHERE i.enabled <> d.enabled
)
SELECT
    @bodytext = 'User: ' + username + ' from ' + hostname + ' ' +
        CASE WHEN new_enabled = 1 THEN 'ENABLED' ELSE 'DISABLED' END + ' ' +
        'SQL Schedule [' + schedule_name + '] at ' + CONVERT(varchar(20),GETDATE(),100),
    @subjecttext = 'SQL Schedule on ' + hostname + ' : [' + schedule_name + '] has been ' +
        CASE WHEN new_enabled = 1 THEN 'ENABLED' ELSE 'DISABLED' END + ' ' +
        'at ' + CONVERT(varchar(20),GETDATE(),100)
FROM cte_schedule

IF @bodytext > ''
BEGIN
    -- send out alert email
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'YourServerMailProfile',
    @recipients = 'DBA_Notify@YourOrg.org',
    @body = @bodytext,
    @subject = @subjecttext

    INSERT INTO dbo.audit_table ( ... )
    SELECT @bodytext, ...
END /*IF*/