Conditional execution after a delete trigger in SQLite

Question

I need to prepare a SQLite trigger for following condition -

There are three tables - retail_store, wholesale_store and product
Tables retail_store and wholesale_store have column product_id from table product

Now I want to write a delete trigger such that if a product is deleted from retail_store and if it is not in table wholesale_store, then that product record should be deleted from product table.

** I understand as a practice it may not be a good idea to delete a product record like this. Please take this question only as a technical complication.

Thanks for considering this one. Cheers!

wenhm · Accepted Answer · 2012-05-24 17:22:55Z

9

Maybe the following sql statemen is useful for you, but i can't assure the syntax is correct.

 CREATE TRIGGER after_retail_store_delete after delete ON retail_store
    WHEN ((select count() from  wholesale_store where productid = OLD.id) = 0)
    BEGIN
      DELETE FROM product WHERE productid = OLD.id ;
    END ;

edited May 24, 2012 at 17:22

answered May 24, 2012 at 16:49

wenhm

1064 bronze badges

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

1 Comment

Vijayendra Over a year ago

AWESOME wenhm!!! It works perfect. I just modified the delete query as the table name should be product. I can't believe why your reputation appears as just '1' while answering this question.

Jason Huntley · Accepted Answer · 2012-05-24 16:21:42Z

Sounds like you don't actually need a trigger. I would consider making use of cascading delete. Are you using foreign keys? Check this out:

The ON DELETE and ON UPDATE action associated with each foreign key in an SQLite database is one of "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE". If an action is not explicitly specified, it defaults to "NO ACTION".

NO ACTION: Configuring "NO ACTION" means just that: when a parent key is modified or deleted from the database, no special action is taken.

RESTRICT: The "RESTRICT" action means that the application is prohibited from deleting (for ON DELETE RESTRICT) or modifying (for ON UPDATE RESTRICT) a parent key when there exists one or more child keys mapped to it. The difference between the effect of a RESTRICT action and normal foreign key constraint enforcement is that the RESTRICT action processing happens as soon as the field is updated - not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint. Even if the foreign key constraint it is attached to is deferred, configuring a RESTRICT action causes SQLite to return an error immediately if a parent key with dependent child keys is deleted or modified.

SET NULL: If the configured action is "SET NULL", then when a parent key is deleted (for ON DELETE SET NULL) or modified (for ON UPDATE SET NULL), the child key columns of all rows in the child table that mapped to the parent key are set to contain SQL NULL values.

SET DEFAULT: The "SET DEFAULT" actions are similar to "SET NULL", except that each of the child key columns is set to contain the columns default value instead of NULL. Refer to the CREATE TABLE documentation for details on how default values are assigned to table columns.

CASCADE: A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key. For an "ON DELETE CASCADE" action, this means that each row in the child table that was associated with the deleted parent row is also deleted. For an "ON UPDATE CASCADE" action, it means that the values stored in each dependent child key are modified to match the new parent key values.

Read more here: http://www.sqlite.org/foreignkeys.html#fk_actions

Thanks Jason. There is an issue though. As I specified the product should only be deleted from table product ONLY IF the product record don't exist in table wholesale_store. Its also important to understand that this operation is like if you delete a child record, then delete parent record too, if the child record don't exist in any other table.

Amilcar Zambrano · Accepted Answer · 2017-02-20 21:21:44Z

-1

Hi reading documentation Sqllite Foreing Key use that SQL command:

PRAGMA foreign_keys = ON;

answered Feb 20, 2017 at 21:21

Amilcar Zambrano

194 bronze badges

Collectives™ on Stack Overflow

Conditional execution after a delete trigger in SQLite

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related