A trigger to add 7 days to a date-CodePudding

I have a column start_week and another column end_week. I make a trigger where I set start_week set end_week in 7days, but it doesn't work correctly.

Here is my code:

CREATE TRIGGER T_fin_semana UPDATE of fin_semana on fb_clients 
BEGIN
  UPDATE fb_clients SET fin_semana = 'select date('now',' 6 day'), WHERE nombre=old.nombre; 
END;

the error:

Execution finished with errors.
Result: near "select": syntax error
At line 1:
CREATE TRIGGER T_fin_semana UPDATE of fin_semana on fb_clients 
BEGIN
  UPDATE fb_clients SET fin_semana = select

CodePudding user response：

I believe that you want :-

CREATE TRIGGER T_fin_semana AFTER UPDATE OF fin_semana ON fb_clients 
    BEGIN
     UPDATE fb_clients SET fin_semana = date('now',' 6 day') WHERE nombre=old.nombre; 
     END;

That is you need

to specify one of:-

BEFORE
AFTER
INSTEAD OF

before the triggering action (UPDATE INSERT or DELETE), and

correct the syntax errors in the UPDATE as shown above.

You can use (SELECT date('now',' 6 day')) but there is no need for the SELECT as the expression is fine on it's own.

Tested Using :-

/* Test */
INSERT INTO fb_clients (fin_semana) VALUES('not set');
UPDATE fb_clients SET fin_semana = 'updated';
SELECT * FROM fb_clients;

Which results in :-