Use of SQL Triggers

Groupcall XVault will continue to operate if the SQL database has triggers created within it; you should exercise caution in not causing excessive or recursive locks when invoking triggers.  The safest approach with triggers is to cause a change to an non-XVault table in the same database.  You can create extra tables in the XVault database if you require, however we strongly recommend prefixing them with something unique to your organisation to guard against future table name conflicts.

One of the most common requirements for integrators is to identify rows that have recently been deleted from the XVault database, for example the removal of LearnerPersonal records.  This could, for example, be used to populate a table of LearnerPersonal RefIds that have recently been deleted or recently modified.

Show Examples

For example, if a table was created and indexed as below:

CREATE TABLE XVaultUpdateHistory
(
	TableName		VARCHAR(40)	not null,
	RefId			VARCHAR(64)	not null,
	CreatedDate		DATETIME 	null,
	[ModifiedDate] 	DATETIME 	null,
	Dirty			INT 		not null	DEFAULT 0,
	[Action] 		VARCHAR(3)
	
	CONSTRAINT [XVaultUpdateHistory_PK] PRIMARY KEY CLUSTERED 
	(
		[TableName] ASC,
		[RefId] ASC
	)

);

CREATE INDEX XVUpdateHistory_Dirty ON XVaultUpdateHistory(Dirty);
CREATE INDEX XVUpdateHistory_ModifiedDate ON XVaultUpdateHistory(ModifiedDate);
CREATE INDEX XVUpdateHistory_Action ON XVaultUpdateHistory([Action]);

Then a trigger could be created on LearnerPersonal to pick up delete events:

CREATE TRIGGER after_delete_trigger ON LearnerPersonal FOR DELETE AS
	SET NOCOUNT ON;
	IF EXISTS(SELECT * FROM DELETED)
	BEGIN
		DECLARE @TableName	VARCHAR(40);
		DECLARE @Action		VARCHAR(3);
		DECLARE @RefId		VARCHAR(64);
		DECLARE @ModifiedDate	DATETIME;
	
		SET @TableName = 'LearnerPersonal';
		SET @Action = 'DEL';
		SELECT @RefId = [RefId],@ModifiedDate = [ModifiedDate]
FROM DELETED;
	
		UPDATE XVaultUpdateHistory
			SET
				Action = @Action,
				ModifiedDate = @ModifiedDate,
				Dirty = 1
			WHERE
				TableName=@TableName
				AND RefId=@refid;
				
		IF (@@rowcount=0)
		BEGIN
			-- no history table object was updated so it must not be
			-- in the table yet, go create it instead
			INSERT INTO XVaultUpdateHistory (TableName, RefId, CreatedDate, ModifiedDate, Dirty, Action)
			values(@TableName, @RefId, CURRENT_TIMESTAMP, @ModifiedDate, 1, @Action);
	END;
END;

Similarly, code could be created to populate the same table on Update events too:

CREATE TRIGGER after_update_trigger ON LearnerPersonal FOR UPDATE AS
	SET NOCOUNT ON;
	IF (UPDATE(ModifiedDate))
	BEGIN
		DECLARE @TableName	VARCHAR(40);
		DECLARE @Action		VARCHAR(3);
		DECLARE @RefId		VARCHAR(64);
		DECLARE @ModifiedDate	DATETIME;
	
		SET @TableName = 'LearnerPersonal';
		SET @Action = 'UPD';
		SELECT @RefId = [RefId],@ModifiedDate = [ModifiedDate]
FROM INSERTED;
	
		UPDATE XVaultUpdateHistory
			SET
				Action = @Action,
				ModifiedDate = @ModifiedDate,
				Dirty = 1
			WHERE
				TableName=@TableName
				AND RefId=@refid;
				
		IF (@@rowcount=0)
		BEGIN
			-- no history table object was updated so it must not be			-- in the table yet, go create it instead
			INSERT INTO XVaultUpdateHistory (TableName, RefId, CreatedDate, ModifiedDate, Dirty, Action)
			values(@TableName, @RefId, CURRENT_TIMESTAMP, @ModifiedDate, 1, @Action);
	END;
END;

The use of triggers is outside of the scope of Groupcall support boundary for XVault; as such the above examples are provided as-is with no warranty.