Using the DBVis SQL commander (as I am using "begin" and "end" in this code, I believe this code is executing in a PL SQL manner) I am trying to execute an sql script I have written to be my new "cleaning trigger"
I am trying to use IF EXISTS statements in this script to achieve cleaning ONLY when a certain column does actually contain data (APPROVED or REJECT)
This script aims to NOT use the insert (to other tables) statements (these will set off other triggers on other tables in an undesired way if they are used, even if there is no data for them to insert) unless there actually is (not null) data in a certain column:
begin
IF EXISTS (SELECT *
FROM HUB_SEGMENTS
where APP_OR_REJECT = 'APPROVED' or APP_OR_REJECT = 'REJECTED')
THEN
INSERT INTO
HUB_APPROVED (HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE)
SELECT
HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE
FROM
HUB_SEGMENTS WHERE APP_OR_REJECT = 'APPROVED';
INSERT INTO
HUB_REJECTED (HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE)
SELECT
HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE
FROM
HUB_SEGMENTS WHERE APP_OR_REJECT = 'REJECTED';
DELETE
FROM
"TESTDEMO".HUB_SEGMENTS
WHERE
APP_OR_REJECT = 'APPROVED' or APP_OR_REJECT = 'REJECTED';
DBMS_OUTPUT.PUT_LINE('Trigger: Insert to action Tables Executed');
ELSE
DBMS_OUTPUT.PUT_LINE('Trigger: Insert to action Tables Not Executed');
end;
I unfortunately get the following error message when I try and run this:
[Code: 6550, SQL State: 65000] ORA-06550: line 33, column 4:PLS-00103: Encountered the symbol ";" when expecting one of the following: if
I am pretty sure that this is down to the way that I have structured the IF EXIST
Of course, it may be that using IF EXIST in DBVis SQL commander,in my PL SQL code, isn't permissible - I haven't seen much documentation for this specific statement from oracle. If so, would there be a another way I might try and accomplish what I am trying to do here?
Any guidance would be greatly appreciated
thanks for the helpful comments - those would have worked if I was using non-oracle SQL (probably?). Unfortunately running a PL/SQL script with "exists" on DBVis pointed at an oracle db causes an error - Exists does not work in PL/SQL scripts (but should in single SQL statements apparently).
I found using a variable, in conjunction with IF, solved my issue:
Declare
v_count NUMBER;
BEGIN
select COUNT(*)
INTO v_count
FROM HUB_SEGMENTS
WHERE APP_OR_REJECT is not null; --for each row where %new.columnname is not new --=> this would be far less "expensive" sql query, need to figure out how to syntax this
IF v_count >0
THEN
INSERT INTO
HUB_APPROVED (HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE)
SELECT
HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE
FROM
HUB_SEGMENTS WHERE APP_OR_REJECT = 'APPROVED';
INSERT INTO
HUB_REJECTED (HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE)
SELECT
HUB_SEGMENTS_GUID, TID, SEGMENT, ID, ROLE, UPDATED_BY, APP_OR_REJECT, UPDATED_DATE
FROM
HUB_SEGMENTS WHERE APP_OR_REJECT = 'REJECTED';
DELETE
FROM
"TESTDEMO".HUB_SEGMENTS
WHERE
APP_OR_REJECT = 'APPROVED' or APP_OR_REJECT = 'REJECTED';
DBMS_OUTPUT.PUT_LINE('Trigger: Insert to action Tables Executed');
ELSE
DBMS_OUTPUT.PUT_LINE('Trigger: Insert to action Tables NOT executed');
END IF;
end;
This managed to work for what I was attempting to achieve (not going to the Insert/Delete statements unless there was actually data in a specific column, in order to avoid accidentally setting off other triggers, weirdly)
I was told by a colleague that doing something along the lines of "for each row where %new.columnname is not new =>..." would be a far less cpu intensive sql query, but I need to figure out how to syntax this. For now, the above code works in a PL/SQL, pointed at an oracle Database, in DBVis.
If I find the "for each row..." way, I'll post it here as a comment