# Does SWI-Prolog have trigger system for dynamic database? **URL:** \ **Category:** Discussion\ **Tags:** discussion, how-to, database\ **Created:** [May 20, 2022, 8:31am UTC](https://swi-prolog.discourse.group/t/does-swi-prolog-have-trigger-system-for-dynamic-database/5411 "2022-05-20T08:31:32Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![chansey97](https://avatars.discourse-cdn.com/v4/letter/c/e480ec/32.png) [@chansey97](https://swi-prolog.discourse.group/u/chansey97)\ **Post date:** [May 20, 2022, 8:31am UTC](https://swi-prolog.discourse.group/t/does-swi-prolog-have-trigger-system-for-dynamic-database/5411/1 "2022-05-20T08:31:32Z")
For example, suppose I have the following database in PostgreSQL ```prolog CREATE DATABASE salary; CREATE TABLE emp(NAME text PRIMARY key, salary integer NOT NULL); ``` I can create a trigger which will automatically call a particular function whenever an event occurs: ```prolog CREATE TRIGGER trig_emp_row_insert_before BEFORE INSERT ON emp FOR EACH ROW EXECUTE PROCEDURE trig_emp_row_insert_before_fnc(); CREATE OR REPLACE FUNCTION trig_emp_row_insert_before_fnc() RETURNS TRIGGER AS $$ BEGIN -- You Code -- RETURN NEW; END; $$ LANGUAGE 'plpgsql'; ``` When I call `INSERT INTO emp VALUES('alice', 10);` the function `trig_emp_row_insert_before_fnc` will be automatically executed. Note that the `trig_emp_row_insert_before_fnc` can further insert more records which fire the trigger again (It even can absorb events and so on). Does SWI-Prolog support the trigger system like PostgreSQL? I’d like to create triggers to handle event when some one calls `assert` / `retract`. I found that prolog\_listen/3 seems what I want, but it was placed in Hackers corner. So I’m not sure if this is the correct use. Also, the dynamic database seems weaker than traditional database. For example: 1. It have only “insert”(assertz) and “delete”(retract), but no “update” operation? 2. It can only give me `p/1` not the full term `p(a)` in the example below. Thanks. ---
**Author:** ![EricGT](https://avatars.discourse-cdn.com/v4/letter/e/f1d935/32.png) [@EricGT](https://swi-prolog.discourse.group/u/EricGT)\ **Post date:** [May 20, 2022, 9:11am UTC](https://swi-prolog.discourse.group/t/does-swi-prolog-have-trigger-system-for-dynamic-database/5411/2 "2022-05-20T09:11:37Z")
Have you looked at [library(persistency)](https://www.swi-prolog.org/pldoc/man?section=persistency) yet? The documentation is a bit thin but may be what you seek. I have previously used this on certain occasions. ---
**Author:** ![jan](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/jan/32/4_2.png) [@jan](https://swi-prolog.discourse.group/u/jan)\ **Post date:** [May 20, 2022, 11:46am UTC](https://swi-prolog.discourse.group/t/does-swi-prolog-have-trigger-system-for-dynamic-database/5411/3 "2022-05-20T11:46:07Z")
> [@chansey97](#): > > Also, the dynamic database seems weaker than traditional database. I’d rather say it is different 🙂 It can also do stuff you cannot express in a database such as adding function symbols, have a non-deterministic rule body and deal with logical variables. > [@chansey97](#): > > It have only “insert”(assertz) and “delete”(retract), but no “update” operation? You can wrap these in transaction/1 to achieve an atomic update. That still means the new clause is at the end or at the beginning. An update that maintains the order is not provided. I may add that at some point. Surely if there is a compelling use case. > [@chansey97](#): > > It can only give me `p/1` not the full term `p(a)` in the example below. It gives you the clause that was added. You can use clause/3 to figure out what is in that clause. As for the hackers corner, this is functionality that will stay there. It is a little experimental, for example there is no clean public interface to get the clause details of a deleted clause (see boot/tabling.pl on how to do that anyway). Details may change in the future and there is nothing similar in other Prolog systems AFAIK. Use these facilities to define new high level abstractions such as _incremental tabling_ or a more high level abstraction of constraints wrt. transactions. Both incremental tabling and transactions may be relevant to you. ---
**Author:** ![Boris](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/boris/32/7486_2.png) [@Boris](https://swi-prolog.discourse.group/u/Boris)\ **Post date:** [May 20, 2022, 1:02pm UTC](https://swi-prolog.discourse.group/t/does-swi-prolog-have-trigger-system-for-dynamic-database/5411/4 "2022-05-20T13:02:04Z")
> [@jan](#): > > An update that maintains the order is not provided. This is generally the case in relational databases: a record has no defined position within the table. In contrast, Prolog does maintain a defined order of clauses, and allows adding as either first or last. In a relational DB, if you use an autogenerated key you get some order in practice but still no control over it. The only way to get order is to use a column that you control. This approach works with Prolog, too. ---
**Author:** ![chansey97](https://avatars.discourse-cdn.com/v4/letter/c/e480ec/32.png) [@chansey97](https://swi-prolog.discourse.group/u/chansey97)\ **Post date:** [May 21, 2022, 11:05am UTC](https://swi-prolog.discourse.group/t/does-swi-prolog-have-trigger-system-for-dynamic-database/5411/5 "2022-05-21T11:05:50Z")
> [@jan](#): > > You can use [clause/3](https://www.swi-prolog.org/pldoc/doc_for?object=clause/3) to figure out what is in that clause. It works, thanks. The following program simulates triggers like PostgreSQL. ```prolog :- dynamic emp/2, log/1. :- prolog_listen(emp/2, updated(emp/2)). :- prolog_listen(log/1, updated(log/1)). updated(emp/2, assertz, Ref) :- clause(Fact, _, Ref), Fact =.. [_|Args], format(atom(Msg), "Added a employee name:~w salary:~w", Args), assertz(log(Msg)). updated(emp/2, retract, Ref) :- clause(Fact, _, Ref), Fact =.. [_|Args], format(atom(Msg), "Delete a employee name:~w salary:~w", Args), assertz(log(Msg)). updated(log/1, assertz, Ref) :- clause(Fact, _, Ref), format("updated log/1 ~w~n", [Fact]). ?- assertz(emp(alice, 25)), assertz(emp(bob, 30)), assertz(emp(clark, 40)), retract(emp(bob, _)), listing(emp/2), listing(log/1). updated log/1 log(Added a employee name:alice salary:25) updated log/1 log(Added a employee name:bob salary:30) updated log/1 log(Added a employee name:clark salary:40) updated log/1 log(Delete a employee name:bob salary:30) :- dynamic emp/2. emp(alice, 25). emp(clark, 40). :- dynamic log/1. log('Added a employee name:alice salary:25'). log('Added a employee name:bob salary:30'). log('Added a employee name:clark salary:40'). log('Delete a employee name:bob salary:30'). true. ``` It took me some time to figure out the Reference in clause/3 is exactly the Context in `updated` though. It seems that there is currently no clear document about prolog\_listen/2. For example, the usage of prolog\_listen/2 here is different from the usage of [monotonic tabling](https://www.swi-prolog.org/pldoc/man?section=tabling-tracking). Also, what does the Channel and Closure mean in prolog\_listen/2? > [@jan](#): > > You can wrap these in [transaction/1](https://www.swi-prolog.org/pldoc/doc_for?object=transaction/1) to achieve an atomic update. I have not tried the transaction/1 approach, but I have a question: is it possible to simulate an _Update_ trigger by the transaction/1 approach with prolog\_listen/2? As a classical example, I’d like to limit the salary of an employee to 10%. Notice that Alice’s current salary is 25. I want to increase her salary to 100, but there is a rule in the company which limits the salary increase of an employee to 10%. So when I call `update(emp(alice, 100)),` the result should be `emp(alice, 27.5)` instead of `emp(alice, 100)` (suppose the `update` predicate exists). Is it possible to achieve this requirement by the trigger system above? ---
**Author:** ![jan](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/jan/32/4_2.png) [@jan](https://swi-prolog.discourse.group/u/jan)\ **Post date:** [May 21, 2022, 2:20pm UTC](https://swi-prolog.discourse.group/t/does-swi-prolog-have-trigger-system-for-dynamic-database/5411/6 "2022-05-21T14:20:16Z")
> [@chansey97](#): > > It seems that there is currently no clear document about [prolog\_listen/2](https://www.swi-prolog.org/pldoc/doc_for?object=prolog_listen/2). For example, the usage of [prolog\_listen/2](https://www.swi-prolog.org/pldoc/doc_for?object=prolog_listen/2) here is different from the usage of [monotonic tabling](https://www.swi-prolog.org/pldoc/man?section=tabling-tracking). Also, what does the Channel and Closure mean in [prolog\_listen/2](https://www.swi-prolog.org/pldoc/doc_for?object=prolog_listen/2)? The documentation is terse, but not wrong AFAIK. _Channel_ is the first argument of prolog\_listen/2,3. Possibly _Event_ would have been a better term. _Closure_ is used throughout for _callbacks_. It is not simply the name of a predicate, but a (callable) term that gets additional arguments using call/2, call/3, etc. Thus, **PredicateIndicator** (Action, Context) mean you need a _predicate indicator_ as first argument to listen to a predicate. If there is a matching event, it calls ``` call(Closure, Action, Context). ``` The various _Action_ description describe what the _Context_ is for that action. > [@chansey97](#): > > I have not tried the [transaction/1](https://www.swi-prolog.org/pldoc/doc_for?object=transaction/1) approach, but I have a question: is it possible to simulate an _Update_ trigger by the [transaction/1](https://www.swi-prolog.org/pldoc/doc_for?object=transaction/1) approach with [prolog\_listen/2](https://www.swi-prolog.org/pldoc/doc_for?object=prolog_listen/2)? For what you are trying to do you probably want transaction/3. That allows for a _constraint_ on the new state. Well, your example is a constraint on the _modification_. That might get a bit tricky. There are surely ways to do it though. ---
**Author:** ![drspro](https://yyz2.discourse-cdn.com/free1/user_avatar/swi-prolog.discourse.group/drspro/32/3509_2.png) [@drspro](https://swi-prolog.discourse.group/u/drspro)\ **Post date:** [May 23, 2022, 7:56am UTC](https://swi-prolog.discourse.group/t/does-swi-prolog-have-trigger-system-for-dynamic-database/5411/7 "2022-05-23T07:56:37Z")
In swi-prolog you could also create this kind of lineair mysql data-base. In the past I made with swi-prolog this kind of database in files which does about the same as mysql postgress. It save the records in 1 file en it keeps index strings with file-postions to the record-file in another file ( index file ). With the record-position from the index you know if it is a new record or it is an existing record. For a new record you use Append for the record-file, obtain the position of the record-file (before you write ) write the record, and append a key line in the indexfile with this position. When it is an existing record you know the position in the record file, then first you go to this postion, and then you have to write the record with Fixed length ( same length as what it was written with ) . See the swi-prolog code below, main thing is the seek function. open(Fnabs, update, Sea,[encoding(unicode\_le), lock(write), close\_on\_abort(true)]), Pos2 is Posa / 2, seek(Sea, Pos2, bof, \_),!, % then here write the record advantage is that you dont need postgress or mysql , disadvantage in my opinion is that with postgress or mysql you first have to Create the database ( through ODBC ) where as with this function you create the database on the fly. Append creates it if it doesnt exist.