lundi 30 novembre 2015

Why can't I create a trigger on sql server 2005 using Deleted/Inserted Tables?

I'm currently trying to write a SQL Trigger that will be used in a 2005 SQL Server. This trigger is made to be fired after an insert and an update querry.

The insert part works fine, but there is a different process for the update, since I want to compare the old and new values. Here is the part that is not correctly working :

    DECLARE @temp nvarchar(200)
    SET @temp = (
        SELECT  SPS_VALEUR 
        FROM DELETED 
        WHERE ID_SPSC_COLONNE  = 'CAT-EFORM-ACA-19-8' 
    )

The error i get when i try to save my trigger is :

Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.

The strange thing is that at the very begining of my code i do this querry, and it works fine :

    DECLARE @idTableauModifie nvarchar(200)
    SET @idTableauModifie = (
        SELECT SUBSTRING( ID_SPSC_COLONNE,1, 16) 
        FROM INSERTED
    )

SPS_VALEUR and ID_SPSC_COLONNE have also the same type (nvarchar), and the INSERTED and DELETED tables are not empty.

I looked for other solutions on this site and others but none are working. I tried to do casts, convertions, an inner join on an empty table having varchars types and even a substring(SPS_VALEUR, 1, 200).

Maybe I am doing something wrong, but if anyone has encountered the same problem, please help me.

Thanks

Aucun commentaire:

Enregistrer un commentaire