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
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. – CL. Jul 11 '19 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; ? – marsh-wiggle Jul 11 '19 at 12:34
  • You can use either OLD.id or NEW.id. What should happen if the id is changed? – CL. Jul 11 '19 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!) – marsh-wiggle Jul 11 '19 at 13:32
  • @CL. Are "NEW." and "OLD." a single row or a result sets with all affected rows? – marsh-wiggle Jul 11 '19 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.