Bug report #20775
MSSQL trigger issues
|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|
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:
[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.
#1 Updated by Alexis Roy-L almost 3 years ago
#4 Updated by Alexis Roy-L almost 3 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.