myOMRON Europe - myKnowledge

Triggering A Stored Procedure With MS SQL Server

Introduction

This article is intended to explain how to initiate a stored procedure using a table and a trigger in Microsoft SQL server.

With a stored procedure it is possible to alter several tables at the same time.
A stored procedure is used when all data has to be send at once; the stored procedure checks this data, divides it and send it to different tables.
However the use of stored procedures is not implemented in the NJ Series Controller.
An alternative method is to use a table and a trigger on a table.
When using a table and its trigger it is possible to initiate the stored procedure.
How to achieve this is explained in this article.

Background Information

The information provided in this article is based on the assumption that a connection between a NJ and the database already is achieved.
Please refer to article NJ Series Basic Database Connection Tutorial on how to establish a database connection

How to Execute a Stored Procedure

The first step is to create the table which is used to initiate the stored procedure. Therefor create a table with all the columns which are necessary for example as shown in the image below.

This database exists of 3 tables. Table_1 with 2 columns, Table_2 with 2 columns and Table_3 which in this case is used for the Trigger.

The trigger will copy the proper columns to the subsequent tables.

Table_3 includes all the columns of Table_1 and Table_2.

Trigger Code

To set the trigger, it is possible to configure it manually or open a template.
To use the template open the table and right click on ‘Triggers’ and select ’New Trigger…’

The template method is an easy way to create a trigger.

Within the template the required conditions and actions still need to be defined.
In this application the trigger of Table_3 is used to redirect the data to Table_1 and Table_2 as shown in the image below.

The trigger which will be created with the query above is named ‘Table_3_to_2&1’ and will be activated on any change on Table_3.
Only after an insert command, the transaction of the IDno, available and Delivery_Amount to Table_1 and Table_2 will be done.

When the trigger-code is done, execute the query in order to add the trigger to Table_3.
The trigger will appear in the map Triggers in Table_3 as shown below.

As an example the following Insert instruction is send:
‘Insert into Table_3(IDno, Available, Delivery_Amount) values (12345, 3, 15)’

This will trigger the stored procedure in Table_3 and this will pass the data to Table_1 and Table_2.

After creating the trigger and stored procedure in the database server, the stored procedure can be used in combination with a Sysmac Studio project.

Included is a NJ project with the corresponding program.

If the Trigger is set correctly and the query is handled correct, the tables are (From 1 to 3 in logic order):

Connection to a SQL-server is supported in the NJ501-1*20 range of controllers.

Introduction

Background Information

How to Execute a Stored Procedure

Trigger Code

Reference