Getting "Invalid column name" in trigger-CodePudding

So I'm trying to create a trigger that will update a column called TOTAL_CURSOS from my table TOTALES with the number of courses available. The trigger will occur when I insert a value into my table CURSO. The problem I have is with my int values DURACION and COSTO.

Here's my trigger code:

CREATE TRIGGER [TR_CURSO] ON [CURSO] FOR INSERT
AS
    
    UPDATE
        TOTALES
    SET
        TOTAL_CURSOS = ( SELECT COUNT(CODIGO) FROM CURSO );
    
    INSERT INTO BITACORA (
        MOMENTO,
        DESCRIPCION
    )
    VALUES (
        SYSDATETIME(),
        
        'SE INSERTO EL MAESTRO CON RFC DE: '  
        (SELECT RFC_I FROM INSERTED)  
        ' DEL CURSO: '  
        (SELECT NOMBRE FROM INSERTED)  
        ' CON COSTO DE: ' 
        CAST(COSTO AS nvarchar(10) )  
        ' CON FECHA DE: '   (GETDATE())  
        ' CON LA DURACION DE: '  
        CAST(DURACION AS nvarchar(5))  
        'Y EL CODIGO DE: '  
        (SELECT CODIGO FROM INSERTED)  
        ' POR EL USUARIO: '  
        CAST((CURRENT_USER) AS CHAR)
    );

The problem I currently get is that it doesn't recognize DURACION and COSTO as valid columns.

Here is my table structure for CURSO.

CREATE TABLE CURSO(
    CODIGO                 CHAR(5)   NOT NULL,
    NOMBRE                 CHAR(40)  NOT NULL,
    COSTO                  INT       NOT NULL,
    FECHA_INI              DATE      NOT NULL,
    DURACION               INT       NOT NULL,
    RFC_I                  CHAR(13)      NULL
);

CREATE TABLE TOTALES(
    TOTAL_INSTRUCTORES     INT           NULL,
    TOTAL_CURSOS           INT           NULL,
    TOTAL_ESTUDIANTES      INT           NULL
);

CREATE TABLE BITACORA(
    MOMENTO                CHAR(30)  NOT NULL,
    DESCRIPCION            CHAR(150) NOT NULL
);

CodePudding user response：

Try simpler statements and test that they work as expected, then assemble them into more complex sequences. Don't try to optimize before you have a working solution.

For example try this, change these :

      CAST(COSTO AS nvarchar(10) )  
        ' CON FECHA DE: '   (GETDATE())  
        ' CON LA DURACION DE: '  
        CAST(DURACION AS nvarchar(5))

to:

      CAST((SELECT COSTO FROM INSERTED.CURSO) AS nvarchar(10) )  
        ' CON FECHA DE: '   (GETDATE())  
        ' CON LA DURACION DE: '  
        CAST((SELECT DURACION FROM INSERTED.CURSO) AS nvarchar(5))

It may work or not. The idea is to find what does work by taking small steps.

CodePudding user response：

You can change the insert from values to an insert from select.

CREATE TRIGGER [TR_CURSO] ON [CURSO]
AFTER INSERT
AS
BEGIN
    UPDATE
        TOTALES
    SET
        TOTAL_CURSOS = ( SELECT COUNT(CODIGO) FROM CURSO );
    
    INSERT INTO BITACORA (
      MOMENTO,
      DESCRIPCION
    )
    SELECT 
      SYSDATETIME() AS MOMENTO,
      CONCAT (
        'SE INSERTO EL MAESTRO CON RFC DE: ', RTRIM(RFC_I), 
        ' DEL CURSO: ', RTRIM(NOMBRE), 
        ' CON COSTO DE: ', COSTO, 
        ' CON FECHA DE: ', GETDATE(), 
        ' CON LA DURACION DE: ', DURACION, 
        'Y EL CODIGO DE: ', RTRIM(CODIGO), 
        ' POR EL USUARIO: ', CURRENT_USER
      ) AS DESCRIPCION
    FROM INSERTED;
    
END;

Demo on db<>fiddle here