DEV Community

Shalom Wanjiku
Shalom Wanjiku

Posted on

DATABASE TRIGGER NOT WORKING

This is the trigger function:CREATE OR REPLACE FUNCTION set_session_email(email TEXT) RETURNS VOID AS $$
BEGIN
PERFORM set_config('myapp.session_email', email, true);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION trg_audit()
RETURNS TRIGGER AS $$
DECLARE
table_name "AuditTable";
initiator_email text;
initiator_ip text;
BEGIN
-- Extract the initiator email from the record if it exists
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
initiator_email := NEW.email; -- Assuming the 'email' column exists
ELSIF TG_OP = 'DELETE' THEN
initiator_email := OLD.email; -- Use OLD for DELETE operation
END IF;

-- Retrieve client IP address
initiator_ip := inet_client_addr();

-- Map TG_TABLE_NAME to enum type "AuditTable"
table_name := TG_TABLE_NAME::"AuditTable";

-- Perform logging based on the operation type
IF TG_OP = 'INSERT' THEN
INSERT INTO "Audit" ("id", "initiatorEmail", "initiatorIp", "action", "tableName", "record")
VALUES (uuid_generate_v4(), initiator_email, initiator_ip, 'CREATE', table_name, row_to_json(NEW));

ELSIF TG_OP = 'UPDATE' AND NEW.del = true AND OLD.del = false THEN
-- Soft delete (treat as a DELETE if del is marked true)
INSERT INTO "Audit" ("id", "initiatorEmail", "initiatorIp", "action", "tableName", "record", "prevRecord")
VALUES (uuid_generate_v4(), initiator_email, initiator_ip, 'DELETE', table_name, row_to_json(NEW), row_to_json(OLD));

ELSIF TG_OP = 'UPDATE' THEN
-- Normal update operation (if del field is not changed to true)
INSERT INTO "Audit" ("id", "initiatorEmail", "initiatorIp", "action", "tableName", "record", "prevRecord")
VALUES (uuid_generate_v4(), initiator_email, initiator_ip, 'UPDATE', table_name, row_to_json(NEW), row_to_json(OLD));

ELSIF TG_OP = 'DELETE' THEN
-- Actual DELETE operation
INSERT INTO "Audit" ("id", "initiatorEmail", "initiatorIp", "action", "tableName", "record")
VALUES (uuid_generate_v4(), initiator_email, initiator_ip, 'DELETE', table_name, row_to_json(OLD));
END IF;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Getting this error:2024-09-12T07:56:49.443Z error: api: {"message":"Error in workProfileHandler","error":"\nInvalid prisma.workProfile.create() invocation in\nC:\Users\admin\projects\kazi\src\handlers\work-profile.ts:324:46\n\n 321 }\n 322 } else {\n 323 // Create a new work profile if no data.id is provided\nā†’ 324 workProfile = await prisma.workProfile.create(\nThe column new does not exist in the current database.","stack":"PrismaClientKnownRequestError: \nInvalid prisma.workProfile.create() invocation in\nC:\Users\admin\projects\kazi\src\handlers\work-profile.ts:324:46\n\n 321 }\n 322 } else {\n 323 // Create a new work profile if no data.id is provided\nā†’ 324 workProfile = await prisma.workProfile.create(\nThe column new does not exist in the current database.\n at Ln.handleRequestError (C:\Users\admin\projects\kazi\node_modules\@prisma\client\runtime\library.js:121:7753)\n at Ln.handleAndLogRequestError (C:\Users\admin\projects\kazi\node_modules\@prisma\client\runtime\library.js:121:7061)\n at Ln.request (C:\Users\admin\projects\kazi\node_modules\@prisma\client\runtime\library.js:121:6745)\n at l (C:\Users\admin\projects\kazi\node_modules\@prisma\client\runtime\library.js:130:9633)\n at workProfileHandler (C:\Users\admin\projects\kazi\src\handlers\work-profile.ts:324:21)\n at upsertWorkProfile (C:\Users\admin\projects\kazi\src\controllers\work-profile.ts:29:21)"}
2024-09-12T07:56:49.444Z error: api: {"message":"Error in upsertWorkProfile","error":"An unexpected error occurred.","details":"No details available","stack":"Error: An unexpected error occurred.\n at workProfileHandler (C:\Users\admin\projects\kazi\src\handlers\work-profile.ts:371:11)\n at upsertWorkProfile (C:\Users\admin\projects\kazi\src\controllers\work-profile.ts:29:21)"}
2024-09-12T07:56:49.445Z error: api: An unexpected error occurred.

Top comments (0)