Hi all,

here's a problem from a german newsgroup, I tested it on my own (with FB
1.5.2)


CREATE TABLE SOME_DATA (
SOME_ID INTEGER NOT NULL,
SOME_TEXT VARCHAR(20),
SOME_DELETED TIMESTAMP,
CONSTRAINT PK_SOME_DATA PRIMARY KEY ( SOME_ID )
);

Then create a view

CREATE VIEW V_SOMEDATA_UNDELETED ( SOME_ID, SOME_TEXT )
AS
SELECT SOME_ID, SOME_TEXT
FROM SOME_DATA
WHERE SOME_DELETED IS NULL;

which will show just the undeleted records.


Now add a BEFORE DELETE trigger to the view:

SET TERM #;
CREATE TRIGGER BD_V_SOMEDATA FOR V_SOMEDATA_UNDELETED
BEFORE DELETE POSITION 0
AS
BEGIN
UPDATE SOME_DATA
SET SOME_DELETED = CURRENT_TIMESTAMP
WHERE SOME_ID = OLD.SOME_ID;
END #
SET TERM ;#

When you're deleting from the view, the record in the table will be
updated *AND* deleted.

If you change the view to a JOINED select then the trigger does what it
should - just update the record.

Is that as designed?


Luc.