mardi 30 avril 2019

Is there a way to get SQLServer Triggers to work with Eloquent queries?

I am developing an application using Laravel 5.5 and SQLServer. I was asked to create a 'History' table, in which we keep track of UPDATE actions. For example, if we update the description of a ticket, we insert the id, the field, the previous value and the new value of that field.

Usually, this is done with Triggers in the Database, so I have set up an after_update trigger.

Now, my trigger works, but not when the queries are written with Eloquent. If I use the PDO object of the same connection used by my Models, the triggers work. If I write a query in the Database interface, using the same connection, they work. But if I write the same query with Eloquent, the field is updated, but the triggers do not fire.

I am aware that Observers exist to act like triggers, and I did set them up to do pretty much the same thing. But, I don't understand why the triggers do not work, and i wonder if it is a normal behavior or if my set up is faulty in some way.

My connection in the database.php file looks like this (with default values i removed here) :

'uti' => [
            'driver'   => 'sqlsrv',
            'host'     => env('DB_HOST'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'prefix'   => '',
            'charset'   => 'iso-8859-1',
            'characterset' => 'iso-8859-1',
            'collation' => 'French_CI_AS',
        ],

The code with which the trigger works :

use Illuminate\Support\Facades\DB;

$oPdo = DB::connection('uti')->getPdo();
$sQuery = $oPdo->prepare("
            UPDATE TICKET SET
            T_DESC = :sDesc
            WHERE T_CODE = :iCode");
$sQuery->execute([':sDesc' => $sDesc, ':iCode' => $code]);

The code with which it does not work :

$oTicket = Ticket::find($code);
        $oTicket->T_DESC = $sDesc;
        $oTicket->save();

The trigger is something like this (with more 'IF UPDATE()' for each field of the table TICKET):

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[after_update_ticket]
ON [dbo].[TICKET] 
AFTER UPDATE
AS
BEGIN -- begin trigger
SET NOCOUNT ON

IF EXISTS (SELECT * FROM INSERTED) And EXISTS(SELECT * FROM DELETED)
-- Is an update --
BEGIN -- begin if update
    DECLARE @user int = 1;
        IF NOT UPDATE (MODIFICATION_DATE) AND NOT UPDATE (MODIFICATION_USER)
            BEGIN -- begin if not update
-- If database connection user is letter + number, user id = number
            IF (ISNUMERIC(SUBSTRING(USER_NAME(USER_ID (CURRENT_USER)),2, LEN(USER_NAME(USER_ID (CURRENT_USER)))))=1)
                BEGIN
                    SET @user = SUBSTRING(USER_NAME(USER_ID (CURRENT_USER)),2, LEN(USER_NAME(USER_ID (CURRENT_USER))));
                END

            -- Update MODIFICATION date and user --

                UPDATE [TICKET] SET 
                MODIFICATION_DATE = CURRENT_TIMESTAMP, 
                MODIFICATION_USER = @user
                WHERE CODE = (SELECT T_CODE FROM inserted)


            -- History trigger --
            IF UPDATE(T_DESC)
                BEGIN
                INSERT INTO HISTORIQUE 
                (H_DATE,H_TABLE,H_FIELD,H_BEFORE,H_AFTER,H_CODE,CREATION_GUT,CREATION_DATE) 
                VALUES 
                (CURRENT_TIMESTAMP,'TICKET','T_DESC',(SELECT T_DESC FROM deleted),(SELECT T_DESC FROM inserted),(SELECT T_CODE FROM inserted),@user,CURRENT_TIMESTAMP)
                END
END -- end if no update

END -- end if update

END -- end trigger


I have tried researching triggers, laravel and eloquent, but looking at the docs and several stackoverflow questions did not provide me with information about the expected behavior of Triggers with Eloquent. I found that some people created them manually with migrations, so I am supposing they are supposed to work, but I could not find information to help me.

Thank you.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire