Industrial Automation
Industrial Automation | Europe

myKnowledge

Main > Product Type > Automation Systems > Machine Controllers
Minimize Text   Default    Enlarge Text
 

Print
E-mail

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.

 

The SQL-server used in this example is Microsoft SQL server 2014.

 

 Reference

Stored Procedure (http://msdn.microsoft.com/en-us/library/ms190782.aspx)



Attachments

SQL_SP.smc2 - Size: 1915074


Comments (View All Comments / Add Comment)

Related Articles
No related articles found.
Created 2014-12-02
Modified 2015-01-13
Views 4723

 

You are not logged in.