{$page}

WARNING: If you are interested in using SQL Bridge, please contact a sales or implementation consultant at Cyrious.

Cyrious Control's SQLBridge is a collection of SQL Server stored procedures and functions and external modules that can be called to safely insert or update data into the Control database and notify the CHAPI service that data has been updated. The CHAPI service is a controller program that will then notify the SSLIP and all copies of Control about the changes.

Caution: Use of this feature requires expertise in SQL Server and in the Cyrious database structure. It is very possible to irreversibly damage your Cyrious Control database if you make a mistake. Always develop your tests using a separate database on a separate machine.

Caution: Always wrap multi-record operations with SQL transaction statements. This ensure the integrity of the entire update by preventing partial changes or records from being saved. If you are not familiar with using SQL transactions, STOP! Do not proceed with any database insertions, deletions, or updates until you are.

This example uses SQL programming to automatically generate a SKU for parts. Anytime a new part is added, if the SKU is blank, a new SKU is generated when the record is saved. This occurs in an Insert Trigger within SQL. The trigger routine then uses SQL Bridge to inform Control that it needs to refresh the record from the database (to pull the new SKU).

code_formatsql

–

– Author: Cyrious Software

– Create date: 2016-03-10

– Description: Automates the creation of the SKU for Parts if not assigned manually

–

CREATE TRIGGER NewPart_AddSKUTrigger

 ON  Part

 AFTER INSERT, Update

AS

BEGIN

1. - SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

SET NOCOUNT ON;

1. - Multiple records can be inserted.

1. - Join the inserted records on the newly updated record to update the SKU if needed

UPDATE Part

SET SeqID = Inserted.SeqID + 1,

1. - Use a simple logic routine here for the SKU = CategoryID.PartID

      SKU = 

	   convert(varchar(12), coalesce(Inserted.CategoryID, 0))

	   +'.'

	   +convert(varchar(12), Inserted.ID)

FROM Inserted

JOIN Part On Inserted.ID = Part.ID

WHERE Coalesce(RTrim(Inserted.SKU), '') = ''

;

1. - Because we can have multiple parts simultaneously added,

1. - we need to iterate through them all and refresh them individually

DECLARE @NewID int;

DECLARE @NewCTID int;

DECLARE my_Cursor CURSOR FAST_FORWARD FOR 

	SELECT ID, ClassTypeID

	FROM INSERTED

	WHERE Coalesce(RTrim(Inserted.SKU), '') = '';

OPEN my_Cursor;

FETCH NEXT FROM my_Cursor into @NewID, @NewCTID;

WHILE @@FETCH_STATUS = 0 

BEGIN 

	EXEC dbo.csf_chapi_refresh @NewID, @NewCTID, -1;

	FETCH NEXT FROM my_Cursor into @NewID, @NewCTID;

END;

CLOSE my_Cursor;

DEALLOCATE my_Cursor;

END

code

Contributor: Cyrious Software

Date: 3/2016

Version: Control 5.7

• Backlinks include_pagepage_componentbacklinks