Data Event Triggers Overview

Data Event Triggers is a feature of Aeries SIS that allows the program to execute a customized stored procedure when data in certain tables is added or updated. The customized stored procedure must be created by the district’s database administrator. This will allow the customer to take additional action behind the scenes when certain actions are performed in Aeries. For example, the customer may create code to sync student information to additional systems in real time when a new student is added to Aeries.

DISCLAIMER: The contents of the customized stored procedures are solely the responsibility of the customer. This setup should only be undertaken by an experienced database administrator and will likely also require input from other systems administrators and/or network administrators depending on the scope of actions to be performed. Major damage can be done to your Aeries database and/or connected systems, so Aeries urges you to exercise extreme caution and utilize test environments when developing the stored procedures for Data Event Triggers. Aeries Support staff will not troubleshoot the code in your stored procedures, and depending on the extent of the problem, may be unable to assist if something goes wrong.

Supported Tables and Triggers

Currently, the system supports the following Data Event Triggers:

More tables and triggers will be added in the future.

Data Event Triggers Setup

There is nothing to set up in the Aeries program to make the Data Event Triggers work. Rather, the setup is all done in the Aeries SQL Server database. If the stored procedure corresponding to a particular trigger does not exist, then that trigger will simply not fire. The affected record will still be saved in Aeries as normal. This way, each customer can set up as many or as few triggers as they need.

Note: The stored procedure (if it exists) will be executed after the affected record is saved to the Aeries database. The stored procedure will execute “silently”; that is, nothing will be displayed to the user that indicates whether the stored procedure executed or whether it encountered an error.

Create Stored Procedures

The stored procedures for the Data Event Triggers should be created in SQL Server Management Studio by the database administrator using the CREATE PROCEDURE command. The parameters for each stored procedure will be as follows:

TID: Represents a transaction ID from the Aeries LOG table. This will allow the customer to retrieve relevant LOG table information for the transaction that caused the trigger to be executed.

Primary Key columns: Each primary key column for the affected table is a parameter to the stored procedure. For example: SC,SN for the STU table and ID for the STF table.

Below is sample syntax for the stored procedures that are currently supported. As additional Data Event Triggers are added to the Aeries program, the corresponding stored procedures may be created in the database. Each stored procedure must follow the naming convention of TableName_Event (e.g., STU_RecordAdded).

Trigger – Record added to the STU table

CREATE PROCEDURE [STU_RecordAdded] (@TID int, @SC smallint, @SN int)
AS
BEGIN  
-- Your code here
END

Trigger – Record updated in the STU table

CREATE PROCEDURE [STU_RecordUpdated] (@TID int, @SC smallint, @SN int)
AS
BEGIN  
-- Your code here
END

Trigger – Record added to the STF table

CREATE PROCEDURE [STF_RecordAdded] (@TID int, @ID int)
AS
BEGIN  
-- Your code here
END

Trigger – Record updated in the STF table

CREATE PROCEDURE [STF_RecordUpdated] (@TID int, @ID int)
AS
BEGIN  
-- Your code here
END

If changes need to be made to the code of an existing stored procedure, the syntax is the same, except the CREATE keyword is replaced with the ALTER keyword.

ALTER PROCEDURE [STU_RecordAdded] (@TID int, @SC smallint, @SN int)
AS
BEGIN  
  -- Your updated code here
END

Permissions

The typical setup of Aeries does not include sufficient SQL Server permissions to execute the stored procedures for Data Event Triggers. In addition to creating the stored procedures, the database administrator will need to grant appropriate permissions. In the example below, the SQL Server username used by the Aeries application is AeriesNetUser.

GRANT EXECUTE ON [STU_RecordAdded] TO [AeriesNetUser]
GRANT EXECUTE ON [STU_RecordUpdated] TO [AeriesNetUser]
GRANT EXECUTE ON [STF_RecordAdded] TO [AeriesNetUser]
GRANT EXECUTE ON [STF_RecordUpdated] TO [AeriesNetUser]

If additional Data Event Triggers are supported in the future, remember to grant the appropriate permissions to the new stored procedures.

Additional Considerations

Due to the broad capabilities of these customized stored procedures, including the interaction with other connected systems, the database administrator must take all necessary steps to ensure that the stored procedures are only created and able to be executed in the correct database(s). Please be aware of the following:

• The triggers will only fire when changes are made within the Aeries web application. Changes made using the Aeries Client Version or made directly in SQL Server will not cause the triggers to fire.
• If a copy of a database is restored from backup (for example, as a snapshot copy or sandbox copy), the stored procedures will be part of that copy. Unintended and potentially damaging effects may result from allowing the stored procedures to execute in the database copy. We recommend either dropping the stored procedures from the copy or denying permissions so that they cannot be executed by the Data Event Triggers feature in Aeries. See the examples below for information on dropping the stored procedure or denying permissions.
• After a new year rollover, the stored procedures will continue to exist in the prior year database. Unintended and potentially damaging effects may result from allowing the stored procedures to execute in the prior year database. Depending on the type of actions done by the stored procedures, it may be necessary either to drop the stored procedures from the prior year database or to deny permissions so that they cannot be executed by the Data Event Triggers feature in Aeries.
• When a new Aeries database is created (for example, prior to running the new year rollover process), any custom stored procedures will not be created in the new database. The database administrator needs to create the stored procedures in the new database only when the new database is ready to use in production. See the examples below for information on quickly copying a stored procedure from one database to another.

Example: Dropping a stored procedure. This deletes the entire stored procedure definition, so be sure you have the definition saved somewhere else before dropping the stored procedure.

DROP PROCEDURE [STU_RecordAdded]

Example: Denying permissions on the stored procedure

DENY EXECUTE ON [STU_RecordAdded] TO [AeriesNetUser]

Example: Copying a stored procedure definition from one database to another.

In the Object Explorer window of SQL Server Management Studio, browse to Databases -> {Current Database Name} -> Programmability -> Stored Procedures, and right-click the name of the stored procedure you wish to copy. Next, select Script Stored Procedure As -> Create To -> New Query Editor Window.

The result will look like this. Be sure to change the database name in the USE statement to the name of the new database, then execute the script to create the identical stored procedure in the new database.

USE [DST16000AeriesDemo_ID]
GO

/****** Object:  StoredProcedure [dbo].[STU_RecordAdded]    Script Date: 12/15/2016 3:05:20 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[STU_RecordAdded] (@TID int, @SC smallint, @SN int)
AS
BEGIN  
-- Your code here
END
GO