CODEWITHSUNDEEP
sqlite
Mike O'Sullivan
Mike O'Sullivan

Reputation: 113

trigger with insert inside a select

I'm trying to create a trigger in which, only under certain circumstances, an insert is performed on another table. Consider the following code:

create table journal (
    pk integer primary key autoincrement,
    dsc varchar(10) not null
);

create table users (
    name varchar(30) primary key not null
);

create trigger users_ai
    after insert on users
begin
    select
        case
            when 1 then
                insert into journal(dsc) values('foo')
        end;
end;

I get the following error when I run this code:

Error: near line 10: near "insert": syntax error

In production, the "1" in the when clause would be replaced by a more complex expression. I've also tried "true" and get the same results. I've also tried surrounding the insert statement in parens and get the same results. Any ideas how to accomplish what I want?

Upvotes: 1

Views: 154

Answers (4)

Mike O'Sullivan
Mike O'Sullivan

Reputation: 113

OK, figured it out. Instead of putting a conditional expression in the block of the trigger, I used a when clause. Here's the code that works:

create trigger users_ai
    after insert on users when 1
begin
    insert into journal(dsc) values('foo');
end;

If that when expression is changed to something that returns false (say 0) then the insert isn't done. In production, the expression will sometimes return true, sometimes false, which, of course, is the point of this code. Thanks everybody!

Upvotes: 1

peak
peak

Reputation: 116710

If you look at the syntax diagram for "CREATE TRIGGER", you'll see your attempt just doesn't match. You can, however, simply use the WHEN branch (without needing FOR EACH ROW):

create trigger users_ai
   after insert on users
   when 1 begin
     insert into journal(dsc) values('foo');
end;

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

You can try to use an INSERT ... SELECT and your expression in the WHERE clause.

...
INSERT INTO journal
            (dsc)
            SELECT 'foo'
                   WHERE 1 = 1;
...

1 = 1 needs to be replaced by your Boolean expression.

Upvotes: 0

GMB
GMB

Reputation: 222432

I think that you want a CASE statement, not a CASE expression. 

create trigger users_ai after insert on users
begin
    case
        when ... then insert into journal(dsc) values('foo');
        when ... then ...;
        else ...;
    end case;
end;

Note: if your trigger needs access to the data that was just inserted, its definition should the for each row option.

Upvotes: 0

Related Questions