Triggers allow a set of SQL code to run on several types of SQL actions, such as INSERT, UPDATE, or DELETE. For instance, upon an INSERT, the SQL engine will call the trigger configured for the INSERT action. The trigger specified will have programming logic to perform actions on other systems. With OLE Automation enabled in SQL, the trigger will have the ability to perform actions using COM objects.

User Management Resource Administrator (UMRA) provides a COM object for interaction with other applications. This COM object allows another system to set variables and execute projects which can manage Active Directory accounts, set up Exchange email, manage Lotus Notes, SAP, and update many multiple database systems like PowerSchool (Oracle) or Skyward. The COM object also allows the other application to pull information from UMRA, such as user information, AD information, or variables set up by the UMRA script developer.

Benefits to using Triggers with UMRA

OLE Automation using triggers will allow SQL to execute a UMRA project in the same fashion that websites integrate with UMRA. In a website you can fill in information for a new user account and press the “Submit” button, which executes a UMRA project. With triggers, the help desk or HR staff can use any program they wish to create a new user record. Instantaneously the user account can be created in Active Directory. There is no longer a need to schedule processes (*limitations will be defined later). UMRA gives several methods to create and modify information in AD. UMRA Forms is similar to a website that someone can use, but it’s run in a standalone application provided by Tools4Ever. Automation is a module provided by UMRA to schedule projects for execution at certain times. This is useful for projects that use a lot of network bandwidth (like moving student accounts and copying folders en masse). Automation also provides a COM object to use with other programs, like the web or SQL triggers. With the automation module, in association with the forms module for UMRA, SQL can utilize the same projects that a website, automation or form project might use and can maintain code reusability.

Limitations

OLE automation allows SQL administrators with sysadmin privileges to perform tasks on the operating system environment. This is a known limitation with SQL Server, but if you trust your administrators then there is nothing to be concerned about, since it only gives them access to programs already on the SQL server. When executing a UMRA project through Triggers, execution time must be taken into consideration. For tasks that take a long time, like copying folders, it would be more appropriate to store the task in a database table for later processing. The scripts can be modified to perform the faster tasks during the Trigger execution and insert the long tasks into a process queue (in SQL). This is also better for network traffic since you would want these tasks happening in the off-peak hours.

SQL Server Configuration

* All tasks should be performed on the SQL server or by using the SQL management tools pointed to the SQL server.

• Open the SQL Server Surface Area Configuration tool
• If a Program Compatibility Assistant window pops up press “Run program”
• Click the Surface Area Configuration for Features link
• Expand the SQL server -> Database Engine and enable OLE Automation by checking the appropriate checkbox. Close the SAC tool when complete.

SQL Database Configuration

Within SQL Management Studio, expand the desired database and table. Right-click on the Triggers folder and select “New Trigger”. Modify the CREATE TRIGGER statement to match your desired results, and then execute the blank statement. An example of the modified CREATE statement is below.

CREATE TRIGGER TriggerCreate ON TriggerDemo.dbo.Users AFTER INSERT

Trigger Modification for UMRA integration

Expand or refresh the Triggers folder in SQL Management Studio to view the new trigger. Double-click on it to modify the code. Paste in the supplied code and modify the supplied code in the appropriate areas and execute the ALTER TRIGGER statement:

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: Allan Bogh -- Create date: 1/20/2009 -- Description: UMRA Create User Trigger -- ============================================= ALTER TRIGGER [TriggerCreate] ON [TriggerDemo].[dbo].[Users] AFTER INSERT AS -- required variable declarations -- DECLARE @UMRAScript varchar(64) DECLARE @UMRAServer varchar(64) DECLARE @UMRAPort int -- EDIT THESE VALUES AS NECESSARY -- SET @UMRAScript = 'TriggerDemo' SET @UMRAServer = 'YourServerName' SET @UMRAPort = 56814 -- EDIT THESE VARIABLE NAMES TO MATCH THE SQL COLUMN INFORMATION -- -- WHICH WILL BE PASSED TO UMRA -- DECLARE @FirstName varchar(50) DECLARE @LastName varchar(50) DECLARE @Department varchar(50) DECLARE @EmployeeId int -- EDIT THESE VARIABLES AND SELECT STATEMENTS TO MATCH -- -- THE INFORMATION COMING FROM THE DATABASE -- SET @FirstName = (SELECT FirstName FROM inserted) SET @LastName = (SELECT LastName FROM inserted) SET @Department = (SELECT Department FROM inserted) SET @EmployeeId = (SELECT ID FROM inserted) -- DO NOT EDIT THE FOLLOWING VARIABLES DECLARE @object int DECLARE @umra int DECLARE @ErrorSource varchar(255) DECLARE @ErrorDesc varchar(255) EXEC @umra = sp_OACreate 'UmraCom.UMRA',@object OUT IF @umra = 0 BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; ------------ DO NOT EDIT BELOW --------------- ---------------------------------------------- EXEC @umra = sp_OAMethod @object,'Connect',NULL,@UMRAServer,@UMRAPort IF @umra <> 0 BEGIN EXEC sp_OAGetErrorInfo @object,@ErrorSource OUT, @ErrorDesc OUT SELECT 'Error occurred calling object: '+ @ErrorSource + ' ' + @ErrorDesc END ------------ DO NOT EDIT ABOVE --------------- ---------------------------------------------- ELSE BEGIN --EDIT the UMRA variable names and associated SQL variables here------------ ---------------------------------------------------------------------------- EXEC @umra = sp_OAMethod @object,'SetVariableText',NULL,'%FirstName%',@FirstName EXEC @umra = sp_OAMethod @object,'SetVariableText',NULL,'%LastName%',@LastName EXEC @umra = sp_OAMethod @object,'SetVariableText',NULL,'%Department%',@Department EXEC @umra = sp_OAMethod @object,'SetVariableText',NULL,'%EmployeeId%',@EmployeeId --EDIT ABOVE---------------------------------------------------------------- ---------------------------------------------------------------------------- -------------DO NOT EDIT BELOW ------------- -------------------------------------------- IF @umra <> 0 BEGIN EXEC sp_OAGetErrorInfo @object,@ErrorSource OUT, @ErrorDesc OUT SELECT 'Error occurred calling object: '+ @ErrorSource + ' ' + @ErrorDesc END ELSE BEGIN -- Executes the specified UMRA script EXEC @umra = sp_OAMethod @object,'ExecuteProjectScript',NULL,@UMRAScript IF @umra <> 0 BEGIN EXEC sp_OAGetErrorInfo @object,@ErrorSource OUT, @ErrorDesc OUT SELECT 'Error occurred calling object: '+ @ErrorSource + ' ' + @ErrorDesc END ELSE EXEC sp_OADestroy @object END END END SET NOCOUNT OFF;

Test trigger by inserting, updating, or deleting rows from the database depending on which triggers you have in place. Check the UMRA logs or AD for project execution.

Testing the Trigger

• Create a project in UMRA called “TriggerTest” (or whatever name you might have changed it to).
• Set the security for the project by Right-clicking the script and selecting Project Script Properties, then Security (include your account or group, and the SQL server account).
• Open SQL Management Studio or the application which submits new records to your table.
• Insert a new record into the table if you created an INSERT trigger, update a record if you created an UPDATE trigger, or delete a record if you created a DELETE trigger.
• Open the UMRA logs under C:Program FilesUmraServiceLog (the newest modified file is the appropriate log file). Scroll to the bottom of the file and view the messages.