cancel
Showing results for 
Search instead for 
Did you mean: 

Error creating sap hana trigger

mario_galeano
Participant
0 Kudos
567

When trying to create an audit trigger for a SAP Hana table, I get the following error:

 

 

SQL Error [1287] [HY000]: SAP DBTech JDBC: [1287] (at 596): identifier must be declared: NEW: line 14 col 13 (at pos 596)

 

 

The code I am using is:

 

 

CREATE TRIGGER AUDIT_PRECIOS_INSERT
AFTER INSERT ON "ASWAP_DBPRUEBAS_AT2"."LISTAS_PRECIOS"
FOR EACH ROW
BEGIN
    DECLARE cliente_host NVARCHAR(256);
    DECLARE cliente_ip VARCHAR(45);
    DECLARE usuario NVARCHAR(256);
    
    SELECT "CLIENT_HOST", "CLIENT_IP", "USER_NAME" INTO cliente_host, cliente_ip, usuario
    FROM "M_CONNECTIONS"
    WHERE "CONNECTION_ID" = CURRENT_CONNECTION;
    
    INSERT INTO "ASWAP_DBPRUEBAS_AT2"."LISTAS_PRECIOS_AUDIT" ("ItemCode", "Cod_Lista_Precio", "Nuevo_Precio", "Fecha", "Usuario_App", "Cliente_Host", "Cliente_IP", "User_Name")
    VALUES (:NEW."ItemCode", :NEW."Cod_Lista_Precio", :NEW."Precio", CURRENT_TIMESTAMP, usuario, cliente_host, cliente_ip, usuario);
END;

 

 

View Entire Topic
JoeyLi
Product and Topic Expert
Product and Topic Expert
0 Kudos

The error you're encountering indicates that the identifier `NEW` must be declared, which means that the trigger does not recognize `NEW` as a valid reference to the newly inserted row. In SAP HANA, the correct way to reference the new row in a trigger is using the `:new` (lowercase) prefix for each column of the inserted row.

Here is a sample:

CREATE TRIGGER AUDIT_PRECIOS_INSERT
AFTER INSERT ON "ASWAP_DBPRUEBAS_AT2"."LISTAS_PRECIOS"
REFERENCING NEW ROW AS new_row
FOR EACH ROW
BEGIN
    DECLARE cliente_host NVARCHAR(256);
    DECLARE cliente_ip VARCHAR(45);
    DECLARE usuario NVARCHAR(256);
    
    SELECT "CLIENT_HOST", "CLIENT_IP", "USER_NAME" INTO cliente_host, cliente_ip, usuario
    FROM "M_CONNECTIONS"
    WHERE "CONNECTION_ID" = CURRENT_CONNECTION;
    
    INSERT INTO "ASWAP_DBPRUEBAS_AT2"."LISTAS_PRECIOS_AUDIT" ("ItemCode", "Cod_Lista_Precio", "Nuevo_Precio", "Fecha", "Usuario_App", "Cliente_Host", "Cliente_IP", "User_Name")
    VALUES (new_row."ItemCode", new_row."Cod_Lista_Precio", new_row."Precio", CURRENT_TIMESTAMP, usuario, cliente_host, cliente_ip, usuario);
END;
mario_galeano
Participant
0 Kudos
The solution worked perfectly for me, thank you very much for the help