Creating trigger based on multiple conditions in sqlite

Question

Dear amazing programmers, I am trying to create trigger in my application database to delete rows in a table based on multiple condition and deletion in another table, and i could use some help here.

Here is my code for the trigger

CREATE TRIGGER RemoveAccountReports 
AFTER DELETE ON Accounts 
FOR EACH ROW 
WHEN Reports.Parent_Type = ACCOUNT 
BEGIN 
DELETE FROM Reports 
WHERE Parent_ID = OLD.Account_ID; 
END;

I need to delete from Reports table with every deletion in Accounts table where Reports.Parent_ID = Accounts.Account_ID.

But only if Reports.Parent_type = "ACCOUNT"

How can i achieve that ?

thanks in advance.

varro · Accepted Answer · 2018-12-07 16:17:50Z

1

It looks to me that you are missing quotes around ACCOUNT; it should be:

WHEN Reports.Parent_Type = 'ACCOUNT'

(Note that single quotes should be used with strings.)

answered Dec 7, 2018 at 16:17

varro

2,5022 gold badges19 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

varro Over a year ago

Great! Please consider checking the accept button to accept the answer.

ZINE Mahmoud · Accepted Answer · 2018-12-08 06:37:37Z

0

when you execute your statement you gonna have this error :

 no such column: Reports.Parent_Type

the Solution :

CREATE TRIGGER RemoveAccountReports 
AFTER DELETE ON Accounts 
FOR EACH ROW 
BEGIN 
DELETE FROM Reports 
WHERE Parent_ID = OLD.Account_ID AND  Reports.Parent_Type = 'ACCOUNT' ; 
END;

edited Dec 8, 2018 at 6:37

answered Dec 7, 2018 at 16:21

ZINE Mahmoud

1,3423 gold badges17 silver badges33 bronze badges

2 Comments

Ahmed Afifi Over a year ago

android.database.sqlite.SQLiteException: no such column: ACCOUNT (code 1): , while compiling: DELETE FROM Accounts WHERE Account_ID = 1

ZINE Mahmoud Over a year ago

hey Please can you post your Class that extends SQLiteOpenHelper to see your Database Structutre and understand from where come this problem ?

Collectives™ on Stack Overflow

Creating trigger based on multiple conditions in sqlite

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related