on 2024 Feb 21 9:50 PM
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;
Request clarification before answering.
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
113 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.