I've got a set of triggers in my table in SQL Server and when I execute the queries in SQL Server Management Studio they work fine. But when they're executed from my php files they doesn't take effect. I used SQL Server Profiler and the trigger gets to the last line of execution that is the insert in my log table but then after this I get an Attention error. Here's my trigger for a delete statement:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery25.sql|7|0|C:\Users\ADMINI~1\AppData\Local\Temp\3\~vsB4EE.sql
ALTER TRIGGER [dbo].[OperationStructureFields_delete]
ON [dbo].[OperationStructureFields]
FOR DELETE
AS
BEGIN
DECLARE
@id INT,
@result varchar(MAX),
@user varchar(MAX),
@LoopCounter INT = 1,
@MAX INT,
@Column NVARCHAR(100),
@Type NVARCHAR(100),
@Value NVARCHAR(100),
@ValueXML xml,
@Sql NVARCHAR(MAX),
@Tmp NVARCHAR(MAX),
@LoopCounter2 INT = 1,
@MAX2 INT,
@Message nvarchar(2048)
SELECT @user = system_user
SELECT @MAX = MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'OperationStructureFields'
SELECT @MAX2 = MAX(ID) FROM deleted
SELECT @LoopCounter2 = MIN(ID) FROM deleted
Select * into #deleted from deleted
WHILE(@LoopCounter2 <= @MAX2)
BEGIN
SET @LoopCounter = 1
SET @result = '{'
WHILE(@LoopCounter <= @MAX)
BEGIN
SELECT @Column = COLUMN_NAME, @Type = DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS WHERE ORDINAL_POSITION = @LoopCounter and TABLE_NAME = 'OperationStructureFields'
if (@Type = 'xml')
BEGIN
SET @Sql = 'SELECT @ValueXML = ' + @Column + ' FROM #deleted where ID=' + CONVERT(varchar(200),@LoopCounter2,0)
exec sp_executesql @Sql, N'@ValueXML xml out', @ValueXML out
SET @Value = CONVERT(VARCHAR(MAX),@ValueXML,0)
END
ELSE
BEGIN
SET @Sql = 'SELECT @Value = ' + @Column + ' FROM #deleted where ID=' + CONVERT(varchar(200),@LoopCounter2,0)
exec sp_executesql @Sql, N'@Value varchar(MAX) out', @Value out
END
IF (@Value is not null or @Value != '') and (@Type = 'datetime2' or @Type = 'datetime' or @Type = 'date')
BEGIN
IF @result = '{'
BEGIN
SET @result = @result + ' "' + @Column + '":"' + CONVERT(VARCHAR(20),@Value,120) + '"'
END
ELSE
BEGIN
SET @result = @result + ', "' + @Column + '":"' + CONVERT(VARCHAR(20),@Value,120) + '"'
END
END
ELSE IF (@Value is not null or @Value != '')
BEGIN
IF @result = '{'
BEGIN
SET @result = @result + ' "' + @Column + '":"' + @Value + '"'
END
ELSE
BEGIN
SET @result = @result + ', "' + @Column + '":"' + @Value + '"'
END
END
SET @LoopCounter = @LoopCounter + 1
END
SET @result = @result + '}'
INSERT INTO sys_logs (username,datahora,tabela,[object_id],[action],oldvalue) values (@user,GETDATE(),'OperationStructureFields',@LoopCounter2,'DELETE',@result)
delete from #deleted where ID = @LoopCounter2
select @LoopCounter2 = MIN(ID) from #deleted where ID > @LoopCounter2
END
END
And a print of my SQL Server Profiler:
The PHP code is:
$res = sqlsrv_query($connection, $_sql, array(), array('Scrollable' => 'buffered'));
and the variable $_sql has the following value:
DELETE FROM OperationStructureFields WHERE ID= '66817'
And it doesn't return any errors.
The PHP version is 5.5.16.
I executed the SELECT @@OPTIONS inside my php file and got the following options:
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL
The only difference to the SQL Server Management Studio is the option ARITHABORT that is ON in the SQL Server Management Studio.
Do you have any ideia what may be causing this?
UPDATES:
I believe it may be something on my PHP settings. In my phpinfo() I've got the following settings for sqlsrv:
Also in SQL Server Profiler I've got the following definitions in Audit Login:
-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
