Geek asks and answers

Conditional execution after delete trigger in SQLite

I need to prepare an SQLite trigger for the following condition -

  • There are three tables - retail_store, wholesale_store and product
  • The retail_store and wholesale_store tables have a product_id column from a table product.

Now I want to write a delete trigger so that if the product has been removed from retail_store and if it is not in the wholesale_store table, then this product record should be deleted from the product table.

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

Thanks for thinking about this. Hooray!

+5
source share
3 answers

, sql statemen, .

 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 ;
+8

, . . ? :

ON DELETE ON UPDATE, SQLite "NO ACTION", "RESTRICT", "SET NULL" , "SET DEFAULT" "CASCADE". , "NO ACTION".

: " " : , .

RESTRICT: RESTRICT , ( ON DELETE RESTRICT) ( ON UPDATE RESTRICT) , . RESTRICT , RESTRICT , , , , , . , , , RESTRICT SQLite , .

SET NULL: - "SET NULL" , , ( ON DELETE SET NULL) ( ON UPDATE SET NULL), , , SQL NULL.

SET DEFAULT: "SET DEFAULT" "SET NULL" , , , NULL. . CREATE TABLE , .

CASCADE: "CASCADE" . " "CASCADE", , , , . "ON "UPDATE CASCADE", , , .

: http://www.sqlite.org/foreignkeys.html#fk_actions

+1

Hi, reading the Sqllite Foreing Key documentation uses this SQL command:

PRAGMA foreign_keys = ON;
-1
source

More articles:


All Articles