CODEWITHSUNDEEP
sqlsql-servertriggers
Javier M
Javier M

Reputation: 17

T-SQL Query works manually but not in a trigger

I have the following query that works flawlessly when executing it manually. This passes some worker information from Table TRABAJADORES to PERSONAL.

INSERT INTO REPORTESDIARIOS.DBO.PERSONAL (DNI, Nombres, Apellidos, Contraseña, IdGrupo, Activo, Confianza)
    SELECT          
        CODTRAB, UPPER(NOMBRE), (UPPER(APEPAT) + ' ' + UPPER(APEMAT)),   
        ENCRYPTBYPASSPHRASE('password', '1234'), 12, 1, 0
    FROM            
        PIL.DBO.TRABAJADORES
    LEFT OUTER JOIN 
        ReportesDiarios.DBO.PERSONAL ON CODTRAB = DNI
    WHERE           
        DNI IS NULL

but when I use it inside a trigger

ALTER TRIGGER COPYNONLISTED
ON TRABAJADORES 
AFTER INSERT, UPDATE
AS 
BEGIN
    INSERT INTO REPORTESDIARIOS.DBO.PERSONAL (DNI, Nombres, Apellidos, Contraseña, IdGrupo, Activo, Confianza)
        SELECT          
            CODTRAB, UPPER(NOMBRE), (UPPER(APEPAT) + ' ' + UPPER(APEMAT)), 
            ENCRYPTBYPASSPHRASE('password', '1234'), 12, 1, 0
        FROM            
            PIL.DBO.TRABAJADORES
        LEFT OUTER JOIN
            ReportesDiarios.DBO.PERSONAL ON CODTRAB = DNI
        WHERE           
            DNI IS NULL
END
GO

it just inserts the id (which is DNI) and the other fields are blank.

ex. Edit: (This is what i get on SQL

Table 1 (row that is not on table 2)

1, Samuel, Jackson, PASS, 1, 0

using the query manually copies the same thing

1, Samuel, Jackson, PASS, 1, 0

however if i use it inside a trigger it just copy the ID but everything else that involves a Query to TRABAJADORES it doesn't work it just returns me blank (" ") data

1,  ,  , PASS, 1,0

is there something I am missing, because I had a similar problem while using the inserted table (and I gave up that approach) so I'm not sure if there is a restriction within the table or with SQL.

Upvotes: 0

Views: 180

Answers (1)

Sean Lange
Sean Lange

Reputation: 33581

OK I think I finally understand what you are trying to do. You need to utilize the inserted and deleted virtual tables in your triggers but apparently you were having some issue with it. Your code was close but it was using TRABAJADORES as the base table instead of inserted. Notice I also added aliases to your query and reference each column starting with the alias. That makes things a LOT easier to determine which table a certain column belongs to.

create TRIGGER COPYNONLISTED
ON TRABAJADORES 
AFTER INSERT, UPDATE
AS 
BEGIN
    INSERT INTO PERSONAL 
    (
        DNI
        , Nombres
        , Apellidos
        , Contraseña
        , IdGrupo
        , Activo
        , Confianza
    )
    SELECT          
        i.CODTRAB
        , UPPER(i.NOMBRE)
        , (UPPER(i.APEPAT) + ' ' + UPPER(i.APEMAT))
        , ENCRYPTBYPASSPHRASE('password', '1234')
        , 12
        , 1
        , 0
    FROM inserted i
    LEFT OUTER JOIN PERSONAL p ON i.CODTRAB = p.DNI
    WHERE p.DNI IS NULL
END
GO

Upvotes: 1

Related Questions