"instead of" insert trigger causing trouble

Suite and Optimus Suite 5
#1

I have implemented logic for an audit trail using MS SQL (SQL Server 2012). I use triggers to implement it.

Unfortunately, this seems to cause me a lot of grief.

I first noticed that using an “after insert” trigger caused havock. I suppose it was due to the fact that the DHTMLX logic uses scope_identity() to know what was last inserted. (In my case, the last inserted record after the “after insert” trigger was the record inserted in the audit trail table.)

So I moved the logic to an “instead of” (no before trigger with MS SQL) insert trigger but that seems also to create problems (first AddRow works, second errors), for a reason I can’t fathom. As far as I know the instead trigger works: inserts first into the audit trail table then into the standard table (called entries below).

This is what I see in the PHP log (shortened for readability) :

[code]===================================
Log started, 30/06/2014 12:47:51

DataProcessor object initialized
1404103671399_gr_id => 1404103671399
1404103671399_c9 => 424
1404103671399_c10 => 424
1404103671399_!nativeeditor_status => inserted
ids => 1404103671399

Row data [1404103671399]
entriesId => 1404103671399
eventNbr => 424
deleteEventNbr => 424
!nativeeditor_status => inserted

INSERT INTO entries(…) VALUES (…‘424’,‘424’'…);SELECT SCOPE_IDENTITY() as dhx_id

Edit operation finished
0 => action:inserted; sid:1404103671399; tid:;

Done in 0.014346122741699s

====================================
Log started, 30/06/2014 12:47:52

DataProcessor object initialized
1404103672079_gr_id => 1404103672079
1404103672079_c9 => 425
1404103672079_c10 => 425
1404103672079_!nativeeditor_status =>
ids => 1404103672079

Row data [1404103672079]
eventNbr => 425
deleteEventNbr => 425
!nativeeditor_status =>

Access control: operation blocked

Edit operation finished
0 => action:error; sid:1404103672079; tid:1404103672079;

Done in 0.002769947052002s
[/code]

Note the empty tid on the first insert and the “operation blocked” on the second insert…

When I delete the “instead of insert” trigger I get this (which works well) :

====================================
Log started, 30/06/2014 12:57:44
====================================

DataProcessor object initialized
1404104264419_gr_id => 1404104264419
1404104264419_c9 => 426
1404104264419_c10 => 426
1404104264419_!nativeeditor_status => inserted
ids => 1404104264419

Row data [1404104264419]
entriesId => 1404104264419
eventNbr => 426
deleteEventNbr => 426
!nativeeditor_status => inserted

INSERT INTO entries(...) VALUES (...'426','426'...);SELECT SCOPE_IDENTITY() as dhx_id

Edit operation finished
0 => action:inserted; sid:1404104264419; tid:65;

Done in 0.0083949565887451s



====================================
Log started, 30/06/2014 12:57:45
====================================

DataProcessor object initialized
1404104265122_gr_id => 1404104265122
1404104265122_c9 => 427
1404104265122_c10 => 427
1404104265122_!nativeeditor_status => inserted
ids => 1404104265122

Row data [1404104265122]
entriesId => 1404104265122
eventNbr => 427
deleteEventNbr => 427
!nativeeditor_status => inserted

INSERT INTO entries(...) VALUES (...'427','427'...);SELECT SCOPE_IDENTITY() as dhx_id

Edit operation finished
0 => action:inserted; sid:1404104265122; tid:66;

Done in 0.0068371295928955s

What to do?

#2

If you have some other way to obtain the ID of new record. You can use afterInsert event of connector and from it set correct new id value ( that will result in correct tid )

Also, there is a way to redefine the insert query if you want to use something different from SCOPE_IDENTITY

#3

Stanislav,

I’m not sure I understood everything.

Why should an “instead of insert” trigger whose last insert is in the main table (not the audit trail table) cause any problem?

What is the way to redefine the insert query? (Note that I dont care much about the scope_identity() as such, but it looks like the dhtmlxConnector/DataProcessor needs it.)

In the meantime, I will use the afterInsert event of connector and from it set correctly new id value ( that will result in correct tid. I hope that will fix things.

#4

The connector itself doesn’t care about triggers. The php code need to know the ID of new record. ( This id will be sent to client side, so the next update operations will use the correct ID value ) If trigger somehow breaks the new ID retrieving - connector returns invalid value and further updates will fail for such record.

What is the way to redefine the insert query?

You can subclass SQLSrvDBDataWrapper

class MySQLSrvDBDataWrapper extends SQLSrvDBDataWrapper{ protected function insert_query($data,$request){ $sql = parent::insert_query($data,$request); //modify sql as you need return $sql; } }

and use “MySQLSrvDB” as DB type during connector initialization.

#5

Thanks Stanislav, I retrieve the id field another way and set the tid that way and it works now.