Odtnhj

There are two tables ... the 'master' (tblFile) holds record details of files that have been processed by some java code .. the PK is the file name. A column of interest in this table is the 'status' column (VALID or INVALID).

In the subordinate table (tblAnomaly) there are many records that hold the anomalies from processing each file .. this table has a FK as the file name from tblFile ... and along with other columns of relevant data there is a boolean type column which acts as an acceptance flag of the anomaly. NULL is accept .. # is not.

The user manually works their way through the list of anomalies presented in a swing ListPane and checks off the anomalies as they address the issue in the source file. When all the anomalies have been dealt with i need the status of the file in tblFile to change to VALID so that it can be imported into a database.

Here is the trigger i have settled on having designed the statements individually via an SQL editor .. however, i do not know how to validate/debug the trigger statement after it is loaded to the database, so cannot work out why it does not work ... no action and no feedback!!

CREATE TRIGGER
updateFileStatus
AFTER UPDATE ON tblAnomaly
WHEN 0 = (SELECT COUNT(*) FROM tblAnomaly WHERE tblAnomaly.file_name = tblFile.file_name AND tblAnomaly.accept = '#')
BEGIN
 UPDATE tblFile
 SET tblFile.file_status = 'VALID'
 WHERE tblFile.file_name = tblAnomaly.file_name;
END;

So i worked it out! .. here is the solution that works.

CREATE TRIGGER
updateFileStatus
AFTER UPDATE ON tblAnomaly
WHEN 0 = (SELECT COUNT(*) 
 FROM tblAnomaly 
 WHERE file_name = old.file_name 
 AND accept = '#')

BEGIN
 UPDATE tblFile
 SET file_status = 'VALID'
 WHERE file_name = old.file_name;
END;