A.J.Millan <[EMAIL PROTECTED]> wrote:
I want know if is possible to do a DELETE, INSERT or UPDATE statement
from
inside a TRIGGER.
Yes. Otherwise a trigger would be rather pointless.
Let's say:
CREATE TABLE tableA (
Id INTEGER,
... );
CREATE TABLE tableB (
Ref INTEGER,
... );
CREATE TRIGGER DeleteReferences BEFORE DELETE ON tableA
BEGIN
SELECT CASE
WHEN (SELECT count(*) FROM tableB WHERE Ref = OLD.Id) != 0 THEN
RAISE(ABORT, 'Id field referenced in Table-B')
// These one Work!!
WHEN (SELECT count(*) FROM tableB WHERE Ref = OLD.Id) != 0 THEN
DELETE FROM tableB WHERE Ref = OLD.Id
// I get an error here
END;
END;
You cannot put a DELETE statement inside a SELECT statement. Note that
RAISE() is a function and may appear where an expression can appear.
DELETE is a statement.
Make it
CREATE TRIGGER DeleteReferences BEFORE DELETE ON tableA
BEGIN
SELECT RAISE(ABORT, 'Id field referenced in Table-B')
where exists (SELECT * FROM tableB WHERE Ref = OLD.Id);
DELETE FROM tableB WHERE Ref = OLD.Id;
END;
If there are in fact no records in tableB that match the condition,
DELETE will safely do nothing. You don't need an explicit check first.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
