Bug report #20775

MSSQL trigger issues

Added by Alexis Roy-L almost 6 years ago. Updated almost 6 years ago.

Status:Closed
Priority:Normal
Assignee:Nyall Dawson
Category:Data Provider/MSSQL
Affected QGIS version:3.4.0 Regression?:No
Operating System:Windows 10 Easy fix?:Yes
Pull Request or Patch supplied:No Resolution:duplicate
Crashes QGIS or corrupts data:No Copied to github as #:28595

Description

Greetings,

I have set up trigger in MSSQL to update the area or the length of the feature. On update of existing feature they work fine but the issues comes when new features are inserted in the database.
This error prevent the insertion of any new feature as long as triggers are present for INSERT operations, this does not affect UPDATE triggers.

The error is the following:
Provider errors:
[Microsoft][ODBC SQL Server Driver][SQL Server]The target table 'O18.mn' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. QODBC3: Unable to execute statement

This seems to be a common issue, I think the only modification that needs to be inserted in the existing code would be to add "INTO [database].[schema].[table]" I'm pretty sure the database part is unnecessary but I'll mention it just in case.

I would assume that the error would come from https://github.com/qgis/QGIS/blob/master/src/providers/mssql/qgsmssqlprovider.cpp#L950
and that adding those elements to that string would probably fix it.

History

#2 Updated by Nyall Dawson almost 6 years ago

  • Status changed from Open to Feedback

Duplicate of #20592?

#3 Updated by Alexis Roy-L almost 6 years ago

Yes this is a duplicate, sorry, it appears 20592 got solved by adding the INTO after the OUTPUT in SQL, this was the solution I was trying to propose.

#4 Updated by Alexis Roy-L almost 6 years ago

  • Assignee set to Nyall Dawson

After taking a look at #20592 I noticed that the proposed solution is a workaround and not a proper solution to this issue. The workaround proposed might not even work for every trigger application.

The issue was solved in the trigger statement itself but I'm proposing an attempt to solve the backend/provider by simply adding the INTO statement as MSSQL seem to need the OUTPUT to have a specified destination when there are triggers involved.

I still think a simple fix on line 950 might be able to solve this and prevent having to use the workaround proposed in #20592

This solution shouldn't be too hard to implement and test as I cannot test it on my workstation.

Judging by the documentation about the OUTPUT clause adding an INTO statement before the VALUES as stated in the error message might fix this problem.

#5 Updated by Nyall Dawson almost 6 years ago

Alexis - can we move the discussion to #20592? I'd appreciate Andy's feedback and testing of your proposal too.

#6 Updated by Nyall Dawson almost 6 years ago

  • Status changed from Feedback to Closed

Duplicate of #20592

#7 Updated by Nyall Dawson almost 6 years ago

  • Resolution set to duplicate

Also available in: Atom PDF