DATABASE Trigger

本文介绍了一种使用SQL Server DDL触发器记录数据库模式更改的方法。通过创建触发器来捕捉并记录所有DDL事件到特定表中,有助于跟踪数据库结构的变化。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

DDL Trigger, 2013年的时候用的是AdministratorLog表,2021年更新为Administration.DDL_Log摘自 https://techcommunity.microsoft.com/t5/azure-sql/logging-schema-changes-in-a-database-using-ddl-trigger/ba-p/1950343

-- =============================================
-- Author:					Andreas Wolter, Microsoft
-- Create date:			11-2020
-- Revision History:	
-- Description:			This script demonstrates the implementation of a DDL-Trigger to log all DDL events to a database table for internal use.
--								it is not meant to act as a security-measure
-- Article:					 Logging Schema-changes in a Database using DDL Trigger
-- Applies to:				SQL Server, Azure SQL Database, Azure SQL Database Managed Instance
--=============================================

SET QUOTED_IDENTIFIER ON

-- Change Database
-- in Azure SQL Database remove the "USE Database" statements and open a new connection if necessary
USE DDLWatchdogTrigger
GO
/* Create Schema if not exists */

IF SCHEMA_ID('Administration') IS NULL
BEGIN
	EXEC( 'CREATE SCHEMA Administration;' );
END


 -- if the DDL Trigger already exists we need to diable it to drop and recreate the logging table
IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'Trig_Log_DDL_DATABASE_LEVEL_EVENTS')
DISABLE TRIGGER Trig_Log_DDL_DATABASE_LEVEL_EVENTS ON DATABASE 
GO

/* Create the DDL_Log table */
 IF EXISTS (SELECT * FROM sys.tables
    WHERE SCHEMA_NAME(schema_id) = 'Administration' AND name = 'DDL_Log')
DROP TABLE Administration.DDL_Log
GO

CREATE TABLE Administration.DDL_Log
(
		DDL_Log_ID			int identity			NOT NULL
	,	EventType				nvarchar(50)		NOT NULL
	,	PostTime				datetime2(2)		DEFAULT SYSDATETIME()
	,	SPID						int						NOT NULL
	,	ServerName			nvarchar(100)	NOT NULL
	,	LoginName			nvarchar(100)	NOT NULL
	,	OriginalLogin			nvarchar(100)	NOT NULL
	,	UserName				nvarchar(100)	NOT NULL
	,	Application			nvarchar(250)	NOT NULL
	,	DatabaseName		nvarchar(100)	NOT NULL
	,	SchemaName		nvarchar(100)	NOT NULL
	,	ObjectName			nvarchar(100)	NOT NULL
	,	ObjectType			nvarchar(100)	NOT NULL
	,	TSQLCommand		nvarchar(max)	NOT NULL
	,	EventData				xml					NOT NULL
)
GO
EXEC sys.sp_addextendedproperty
		@name			= N'Description'
	,	@value			= N'Stores all relevant DDL Statements against the current database, inserted by DDL Trigger.'
	,	@level0type	= N'SCHEMA'
	,	@level0name	= N'Administration'
	,	@level1type	= N'TABLE'
	,	@level1name	= N'DDL_Log'
GO

EXEC sys.sp_addextendedproperty
		@name			= N'Referenced by'
	,	@value			= N'PROC: del_DDL_Log_by_oldest_date, TRIGGER: Trig_Log_DDL_DATABASE_LEVEL_EVENTS'
	,	@level0type	= N'SCHEMA'
	,	@level0name	= N'Administration'
	,	@level1type	= N'TABLE'
	,	@level1name	= N'DDL_Log'
GO

ALTER TABLE Administration.DDL_Log
	ADD CONSTRAINT PKCL_Administration_DDL_Log_DDL_Log_ID
		PRIMARY KEY CLUSTERED (DDL_Log_ID)
GO


/* Drop and Re-Create the DDL Trigger */

IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'Trig_Log_DDL_DATABASE_LEVEL_EVENTS')
DROP TRIGGER Trig_Log_DDL_DATABASE_LEVEL_EVENTS ON DATABASE
GO

CREATE TRIGGER Trig_Log_DDL_DATABASE_LEVEL_EVENTS
	ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
/*
-- =============================================
Author:					Andreas Wolter, Microsoft
Create date:			11-2020
Revision History:	

Description:			Database-scope DDL-Trigger to log all Schema-changes

Permissions:			DDL Triggers are executed under the context of the caller - or can be executed under a specific user name using the EXECUTE AS-clause
							Unless a specific User account is used, it needs to be made sure that every potential user who can run DDL statements has also INSERT-Permission to the DDL_Log-Table. It may be fine to use public.
-- =============================================
*/
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;
-- XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON.

DECLARE
			@EventData			xml
		,	@EventType			nvarchar(50)
		,	@TSQLCommand	nvarchar(max)
		,	@PostTime			datetime2(2)
		,	@SPID					int
		,	@ServerName		nvarchar(128)
		,	@LoginName			nvarchar(128)
		,	@Original_Login		nvarchar(128)
		,	@UserName			nvarchar(128)
		,	@Application			nvarchar( 250 )
		,	@DatabaseName	nvarchar(128)
		,	@SchemaName		nvarchar(128)
		,	@ObjectName		nvarchar(128)
		,	@ObjectType			nvarchar(100)

SET @EventData		= EVENTDATA()
SET @EventType		= @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(50)' )
SET @TSQLCommand	= @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)' )
--CONVERT(NVARCHAR(max), @EventData.query('data(//TSQLCommand//CommandText)'))
SET @PostTime			= @EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime2(2)' )
SET @SPID				= @EventData.value('(/EVENT_INSTANCE/SPID)[1]', 'int' )
SET @ServerName		= HOST_NAME()
SET @LoginName		= SYSTEM_USER
SET @Original_Login	= ORIGINAL_LOGIN()
SET @UserName		= USER_NAME()
SET @Application		= COALESCE(APP_NAME(), '** NA **' )
SET @DatabaseName	= DB_NAME()
SET @SchemaName	= CASE WHEN (COALESCE(@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname' ), '** no schema **') = '') THEN '** no schema **' ELSE COALESCE(@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname' ), '** no schema **') END	   -- some events like "GRANT" on a Database return empty string for schema instead of NULL
SET @ObjectName		= @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname' )
SET @ObjectType		= @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'sysname' )

-- disallowing the removal of the DDL-Log Table
-- Disable or Drop the trigger beforehand
IF		@EventType	= 'DROP_TABLE'
  AND	@ObjectName	= 'DDL_Log'
  AND	@SchemaName	= 'Administration'
	BEGIN
		ROLLBACK
	END

-- Filter out operations that do not need to be looged such as Index Maintenance
IF (@EventType NOT IN (
				'UPDATE_STATISTICS'
			--,	'ALTER_INDEX'	-- We do want to include Disabling of indexes
		)
	AND NOT (@EventType = 'ALTER_INDEX' AND @TSQLCommand NOT LIKE '%DISABLE%')
	)
BEGIN

	INSERT INTO Administration.DDL_Log
			   (EventType
			   ,SPID
			   ,ServerName
			   ,LoginName
			   ,OriginalLogin
			   ,UserName
			   ,Application
			   ,DatabaseName
			   ,SchemaName
			   ,ObjectName
			   ,ObjectType
			   ,TSQLCommand
			   , EventData)
		 VALUES
			   (@EventType
			   ,@SPID
			   ,@ServerName
			   ,@LoginName
			   ,@Original_Login
			   ,@UserName
			   ,@Application
			   ,@DatabaseName
			   ,@SchemaName
			   ,@ObjectName
			   ,@ObjectType
			   ,@TSQLCommand
			   ,@EventData)


END;

SET QUOTED_IDENTIFIER OFF

GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.procedures
    WHERE SCHEMA_NAME(schema_id) = 'Administration' AND name = 'del_DDL_Log_by_oldest_date')
DROP PROCEDURE Administration.del_DDL_Log_by_oldest_date
GO

CREATE PROCEDURE Administration.del_DDL_Log_by_oldest_date
	@oldest_date	datetime2(0)
AS

/*
-- =============================================
Author:					Andreas Wolter, Microsoft
Create date:			11-2020
Revision History:	
Description:			Remove old rows from DDL_Log

Execution example:
	
EXEC	Administration.del_DDL_Log_by_oldest_date
		@oldest_date = '2020-11-22'

-- =============================================
*/
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;

DELETE FROM Administration.DDL_Log
	WHERE PostTime < @oldest_date	-- oldest records date to keep

GO


EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Maintenance Procedure: Removes old rows from Administration.DDL_Log-Table' , @level0type=N'SCHEMA',@level0name=N'Administration', @level1type=N'PROCEDURE',@level1name=N'del_DDL_Log_by_oldest_date'
GO


-- === / Begin SECURITY

GRANT INSERT
	ON [Administration].[DDL_Log]
	TO public
GO

-- === / End SECURITY


BEGIN TRY
	ENABLE TRIGGER Trig_Log_DDL_DATABASE_LEVEL_EVENTS
	ON DATABASE;
	PRINT 'DDL Watchdog-Trigger active :)'
END TRY
BEGIN CATCH
	PRINT 'Something went wrong'   
	SELECT   
        ERROR_NUMBER() AS ErrorNumber  
       ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH;



/* =============== Clean up Code
-- run this if you want to drop all related Objects


IF EXISTS (SELECT * FROM sys.procedures
    WHERE SCHEMA_NAME(schema_id) = 'Administration' AND name = 'del_DDL_Log_by_oldest_date')
DROP PROCEDURE Administration.del_DDL_Log_by_oldest_date
GO

IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'Trig_Log_DDL_DATABASE_LEVEL_EVENTS')
DROP TRIGGER Trig_Log_DDL_DATABASE_LEVEL_EVENTS ON DATABASE
GO

 IF EXISTS (SELECT * FROM sys.tables
    WHERE SCHEMA_NAME(schema_id) = 'Administration' AND name = 'DDL_Log')
DROP TABLE Administration.DDL_Log
GO

IF SCHEMA_ID('Administration') IS NULL
BEGIN
	EXEC( 'DROP SCHEMA Administration;' );
END


=============== */ 



/*--=== ALTERNATE SECURITY implementation using a least privileged account during Trigger-execution

If INSERT for Public is not ok, the alternative is to use a specifi User for the Trigger Execution Context.
This is how this could look like:

--===  Create User to write to the DDL-Log-Table and potentially other internal error log tables if not exists
IF USER_ID('DBLogWriter') IS NULL
BEGIN
	CREATE USER DBLogWriter WITHOUT LOGIN
END

-- sticking to best practice to use roles for permission assignment
IF NOT EXISTS (SELECT * FROM sys.database_principals
	WHERE type_desc = 'DATABASE_ROLE'
		AND name = 'RL_DBLogWriter')
BEGIN
	CREATE ROLE RL_DBLogWriter
END

ALTER ROLE RL_DBLogWriter
	ADD MEMBER DBLogWriter
GO

-- now we can grant the Insert-permission onto the whole new schema, even if the table does not exist yet
GRANT INSERT
	ON SCHEMA::Administration
	TO RL_DBLogWriter
GO

-- now change the Tigger header to use:
... WITH EXECUTE AS 'DBLogWriter'

*/

 

 

 

### Zabbix Trigger Configuration and Usage In the context of Zabbix monitoring systems, triggers play a crucial role in alerting administrators about potential issues or anomalies within monitored environments. A trigger is essentially an expression that evaluates data collected from hosts to determine whether a problem exists. #### Definition and Purpose Triggers are logical expressions based on item values or service statuses used by Zabbix to detect problems automatically. They can be configured with different severity levels (Information, Warning, Average, High, Disaster), allowing users to prioritize alerts effectively[^1]. #### Basic Structure of Triggers A typical trigger consists of several components including conditions, dependencies, and actions: - **Conditions**: These define when the trigger should fire using functions such as `last()`, `avg()`, etc., which operate on specific items. - **Dependencies**: One trigger may depend on another; this means it will only activate under certain circumstances defined by other related triggers. - **Actions**: Once triggered, these specify what happens next—such as sending notifications via email/SMS/etc.[^2] Here’s how you might set up basic configurations programmatically through API calls: ```json { "jsonrpc": "2.0", "method": "trigger.create", "params": { "description": "Too many processes running", "expression": "{Template OS Linux:proc.num[]}>500" }, "auth": "<your-auth-token>", "id": 1 } ``` This JSON snippet demonstrates creating a new trigger named *'Too many processes running'* where if more than 500 processes exist (`{Template OS Linux:proc.num[]} > 500`), then the condition becomes true triggering further action(s). Additionally, templates often come pre-configured but custom ones could also include advanced features like macro variables `${}` enhancing flexibility across multiple similar setups without redundancy per host basis alone [^3]. #### Example Scenario – Disk Space Monitoring Suppose we want to monitor disk space utilization exceeding thresholds at varying severities over time intervals dynamically adjusting according priorities accordingly : Trigger Expression : ```bash ({HOST.NAME}:vfs.fs.size[/,pfree].last())<10 And ({HOST.NAME}:vfs.fs.size[/,pfree].min(5m))<=5 ``` Explanation : If free percentage drops below ten percent AND remains less than five percent during last five minutes , raise alarm level appropriately depending upon business impact analysis results derived previously . --- §§Related Questions§§ 1. How do macros function inside template definitions while setting complex multi-level nested conditional statements ? 2. What best practices apply towards optimizing performance metrics collection utilizing proxies alongside centralized servers architecture design considerations? 3. Can there exist any limitations regarding simultaneous execution limits imposed against large scale enterprise deployments involving thousands interconnected nodes requiring real-time updates continuously ? 4. Is it possible integrate external scripts written Python directly into existing workflows managed solely within GUI interface itself rather relying third party plugins/extensions altogether? 5. Which database schema optimization techniques recommended ensuring minimal latency even peak load scenarios especially concerning historical archiving purposes long term storage requirements fulfillment aspects too .
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值