Can a SELECT statement be used within a trigger?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Can a SELECT statement be used within a trigger?

Shane Dev
Hello

The syntax diagram at the top of
https://www.sqlite.org/lang_createtrigger.html implies a SELECT statement
can be used between the BEGIN and END key words.

For example -

sqlite> CREATE TABLE stuff(thing text);
sqlite> CREATE VIEW vstuff as select * from stuff;
sqlite> CREATE TRIGGER tstuff instead of insert on vstuff begin insert into
stuff select new.thing; select * from stuff; end;
sqlite> insert into vstuff select 'object';
-- no output
sqlite> select * from stuff;
thing
object

Here we see the INSERT statement was triggered but not the SELECT. Have I
misunderstood the syntax diagram?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Can a SELECT statement be used within a trigger?

Hick Gunter
INSERT and INSTEAD OF INSERT triggers have no result set. The select will be performed, but it's result set is discarded. Same for UPDATE and INSTEAD OF UPDATE triggers.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Shane Dev
Gesendet: Donnerstag, 21. Dezember 2017 16:12
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Can a SELECT statement be used within a trigger?

Hello

The syntax diagram at the top of
https://www.sqlite.org/lang_createtrigger.html implies a SELECT statement can be used between the BEGIN and END key words.

For example -

sqlite> CREATE TABLE stuff(thing text);
sqlite> CREATE VIEW vstuff as select * from stuff; CREATE TRIGGER tstuff
sqlite> instead of insert on vstuff begin insert into
stuff select new.thing; select * from stuff; end;
sqlite> insert into vstuff select 'object';
-- no output
sqlite> select * from stuff;
thing
object

Here we see the INSERT statement was triggered but not the SELECT. Have I misunderstood the syntax diagram?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can a SELECT statement be used within a trigger?

Simon Slavin-3
In reply to this post by Shane Dev


On 21 Dec 2017, at 3:11pm, Shane Dev <[hidden email]> wrote:

> Here we see the INSERT statement was triggered but not the SELECT. Have I
> misunderstood the syntax diagram?

It’s possible that the SELECT is being processed.  However, since the INSERT command returns no data it still can return no data.

It should be possible to find out if the SELECT is being processed by defining an external function which includes a log statement.  But this information is probably useless to you.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can a SELECT statement be used within a trigger?

David Raymond
In reply to this post by Shane Dev
That's there for use of the raise() function. For example...

create trigger tbl_stop_deletes
before delete on tbl
begin
select raise(abort, 'Not allowing Delete''s from this table');
end;



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Shane Dev
Sent: Thursday, December 21, 2017 10:12 AM
To: SQLite mailing list
Subject: [sqlite] Can a SELECT statement be used within a trigger?

Hello

The syntax diagram at the top of
https://www.sqlite.org/lang_createtrigger.html implies a SELECT statement
can be used between the BEGIN and END key words.

For example -

sqlite> CREATE TABLE stuff(thing text);
sqlite> CREATE VIEW vstuff as select * from stuff;
sqlite> CREATE TRIGGER tstuff instead of insert on vstuff begin insert into
stuff select new.thing; select * from stuff; end;
sqlite> insert into vstuff select 'object';
-- no output
sqlite> select * from stuff;
thing
object

Here we see the INSERT statement was triggered but not the SELECT. Have I
misunderstood the syntax diagram?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users