Skip to Main Content
Need Support? Let’s guide you to the right answer or agent.
Status Needs review
Created by Guest
Created on Oct 11, 2023

Provide a Transform function that can be used to run a stored procedure on the current row

The current trigger arrangement allows you to use SQL statements to set the value of a single attribute based on some input. While it is posable to call a stored procedure to simplify writing these triggers or even update the attributes table, performance can be slow if many attributes need to be updated as a result of a change.

As I understand it the triggers are effectively on before insert/update and therefore updating the table directly in a stored procedure would not have access to the values that are being saved at the time the trigger is activated (unless you pass them in as parameters.

Using functions requires knowledge of C++ and creates maintenance issues as they would need to be deployed to all clients including the Web services gateway.

The ability to call a stored procedure that is passed the current attribute row encoded as JSON and then updates the current document attributes buffer with the transformed result would allow a way of implementing complex dependencies in a single stored procedure without needing excessively log parameter lists and or multiple triggers.

The function could be TransformRow(procedureName:string):int and the stored procedure follow this pattern:-

CREATE PROCEDURE myProcedureName(@CurrentRow nvarchar(max))

BEGIN

... update the values using the built in SQL server support for JSON ...

SELECT @CurrentRow

END

This would make it easier to implement complex rules relating to revision history for example.

  • Guest
    Reply
    |
    Nov 26, 2023

    Another option would be the ability to run a PowerShell script that is stored in the Datasource.

    If the ProjectWise client install included some form of limited sandboxed PowerShell runtime environment that allowed the manipulation of the current document record Attributes from the client-side buffer and the ability to read from other tables as an alternative to passing SQL statements back to the database engine for processing. The script could be stored/maintained in the ProjectWise Datasource and passed to this runtime environment for processing.

  • Guest
    Reply
    |
    Oct 17, 2023

    An alternative would be to enable an environment level trigger that can be used to manipulate the current row in the document attributes buffer that uses the same serialize/deserialize principle.