0

I have a SQLite table with these fields among others

Fld_QuoteNumberRoot     string          
Fld_Revision            integer         
Fld_QuoteNumber         string

I need to fill Fld_QuoteNumber on every UPDATE and on every INSERT like this:

'Q00897' + 4 => 'Q00897/4'

Using this trigger

CREATE TRIGGER trigger_quote_number AFTER UPDATE on Quote_Base
BEGIN
    UPDATE Quote_Base SET Fld_QuoteNumber = printf('%s/%d', Fld_QuoteNumberRoot, Fld_Revision)
END;

I always the error:

near "END": syntax error

Questions
What is wrong with my syntax?

Can I create a trigger which fires on UPDATE and on INSERT or do I need to define two triggers?

Improve this question
0

2 Answers 2

Reset to default
1

The best way to implement this is calculated field:

create table Quote_Base (
    Fld_QuoteNumberRoot string,          
    Fld_Revision        integer,         
    Fld_QuoteNumber     string   GENERATED ALWAYS AS (Fld_QuoteNumberRoot || '/' || Fld_Revision) STORED
);
insert into Quote_Base (Fld_QuoteNumberRoot, Fld_Revision) values ('Q00897', 4);

select * from Quote_Base;

SQLite online fiddle

Improve this answer
0

The UPDATE Quote_Base ... needs a semicolon at the end ... Fld_Revision);

CREATE TRIGGER trigger_quote_number AFTER UPDATE on Quote_Base
BEGIN
    UPDATE Quote_Base SET Fld_QuoteNumber = printf('%s/%d', Fld_QuoteNumberRoot, Fld_Revision);
END
Improve this answer
6
  • This UPDATE will change all rows in the table. Commented Jul 11, 2019 at 10:43
  • @cl is this the correct where clause to restrict it only to the changed row (ID is the primary key): WHERE id = old.id; ? Commented Jul 11, 2019 at 12:34
  • You can use either OLD.id or NEW.id. What should happen if the id is changed? Commented Jul 11, 2019 at 13:22
  • It is intended to change Fld_QuoteNumber only when Fld_QuoteNumberRoot Fld_Revision but I don't show it in my example. So to restrict it only to the affected row I should better use NEW.id. Because the id and one of the other may change in one operation and I guess that in "AFTER UPDATE" the id is set already to the new value. Right? (thanks!) Commented Jul 11, 2019 at 13:32
  • @CL. Are "NEW." and "OLD." a single row or a result sets with all affected rows? Commented Jul 11, 2019 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.