Suspend Trigger on parole

Asked

Viewed 57 times

3

I did not find a SQL statenment IF in Sqlite and the closest was the CASE, that this attending me, meanwhile would like to suspend a Trigger upon a conditional and believe that can only be done with IF

CREATE TRIGGER...
BEGIN
    IF (SELECT COUNT(*) FROM DATA < 900) THEN
       SUSPEND;

    ...
END;

It is possible to do something like this in Sqlite?

sql sqlite sqlite3

  • Not like this the way you want it. The triggers only have DML commands. The best I ever did was to put the condition in any and all where of the Rigger Queries

  • I thought about it but it would be a {IF} to define of do or not a {INSERT} ie in Trigger the {INSERT} could only be executed if meet the condition and I do not see where to put a {WHERE} in this case.

  • has a construction that is the INSERT INTO ... SELECT ..., where the SELECT is a traditional selection. Unfortunately there is no IF nor variables in Sqlite, can not program much

  • Will Trigger’s WHEN is possible to make two conditions, today I already have an AFTER UPDATE ... WHEN field = 1. Ai could put a SELECT COUNT(*)... >= 900

  • never seen WHEN in Sqlite grammar for triggers. Maybe lack of attention from my

  • Thank you for Brainstorm Jefferson.

Show 1 more comment

2 answers

1

For generic SQL, you can use CASE: CASE is used to provide type of logic if-then-else for SQL. Its syntax is:

SELECT CASE ("column_name")
  WHEN "condition1" THEN "result1"
  WHEN "condition2" THEN "result2"
  ...
  [ELSE "resultN"]
  END
FROM "table_name"

Example:

UPDATE pages
SET rkey = rkey + 2,
    lkey = CASE  WHEN lkey >= $key THEN lkey + 2 ELSE lkey END
WHERE rkey >= $key

Documentation link: Here

Other Links that explain well: sqlite.awardspace.info and "Using CASE expressions when modifying data"

1

CREATE TRIGGER ...
AFTER UPDATE ON table WHEN NEW.field = 1
BEGIN
    ...
END

Can also be done with more than one parole:

CREATE TRIGGER ...
AFTER UPDATE ON table WHEN NEW.field = 1 AND (SELECT COUNT(*) FROM table) >= 900
BEGIN
    ...
END

Browser other questions tagged sql sqlite sqlite3

You are not signed in. Login or sign up in order to post.