Dotaz: trigger

[johny]

Ahoj vsem,  poteboval bych poresit jednu vec.

Mam tabulku se zaznamy tvorenymi polozkami x a y, ktera vypada nejak takhle

CREATE TABLE "tab" (

x varchar(64);

y varchar(64);

created DATETIME;

last DATETIME;

UNIQUE ( x, y);

)

Krome polozek x a y obsahuje tabulka i dva casove udaje - kdy byl zaznam porizen a kdy byl naposledy kontrolovan.

Samotne zaznamy by mely byt unikatni, nemenne, pri pokusu o vlozeni stejneho zaznamu by melo dojit jen k prepsani znacky 'last'.

Myslim, ze to neni az tak neobvyykla uloha. Je mozne zajistit preklopeni pokusu o vlozeni na update znacky last pomoci triggeru? Primarne bych to chtel v sqlite, ale nebranim se ani reseni v jinych DB.

 

Odpovědi

[s0]

ano, jde to. A často se to tak dělá.

sqlite:  dokumentace a příklady

[johny]

A mohl bych vedet jak?

Googlit jsem samozrejme zkousel, zkousel jsem par desitek pokusu, ale porad ne a ne prijit na to jak :(

[s0]

snadno

-- Script started
CREATE TABLE tab
(
x VARCHAR2(64),
y VARCHAR2(64),
created DATETIME,
updated DATETIME,
unique(x, y)
);
-- No error
--
CREATE TRIGGER t_tab_insert
    AFTER INSERT ON tab
    FOR EACH ROW
BEGIN
    UPDATE tab SET created = DATETIME('NOW')
         WHERE x = new.x
     AND y = new.y;
END;
-- No error
--
CREATE TRIGGER t_tab_update
    AFTER UPDATE OF x, y ON tab
    FOR EACH ROW
BEGIN
    UPDATE tab SET updated = DATETIME('NOW')
         WHERE x = new.x
     AND y = new.y;
END;
-- No error
--
insert into tab values ('1', '1', null, null);
-- No error
--
insert into tab values ('1', '2', null, null);
-- No error
--
select * from tab;
-- No error
-- x   y   created             updated
-- -   -   ------------------- -------
-- 1   1   2009-01-28 13:34:54 {null}
-- 1   2   2009-01-28 13:34:55 {null}

update tab set x = '2';
-- No error
--
select * from tab;
-- No error
-- x   y   created             updated
-- -   -   ------------------- -------
-- 2   1   2009-01-28 13:34:54 2009-01-28 13:35:36
-- 2   2   2009-01-28 13:34:55 2009-01-28 13:35:36

-- Script finished


Jiné DB neš sqlite mají pochopitelně jiné možnosti. Takže by to šlo udělat jendím triggerem 9IF UPDATING... apod.) ale fakt bude lepší, kdyby sis přešetl nějakou SQL příručku a dokumentaci k tvé DB.

[s0]

kurvafix, ten editor mne zabje...

[johny]

Diky,

  tohle jsem taky zkousel, ale me jde o to, aby kdyz udelam znovu insert toho sameho, tak abych misto hlasky o poruseni omezeni docilil ten update.

sqlite> insert into tab values ('1', '1', null, null);
sqlite> insert into tab values ('1', '2', null, null);
sqlite> insert into tab values ('1', '1', null, null);
SQL error: columns x, y are not unique
 

[s0]

jak rikam, cti dokumentaci ;)

ON CONFLICT REPLACE http://www.sqlite.org/lang_conflict.html

[johny]

Dokumentaci mam prectenou horem dolem, jinak bych se neptal :)

U toho REPLACE se rika, ze pre-existing rows that are causing the constraint violation are removed. To mi ale vadi, protoze tim prijdu o puvodni hodnotu sloupce created. To uz mi prislo zajimavejsi to IGNORE ale s tim jsem taky niceho nedosahl.

Idealni by bylo, kdy k tomu ON CONFLICT sla pripojit vlastni procedura nebo by sla odchytit ta chybova vyjimka, ale to tady nejde. Napadlo me i odstranit ten constraint a delat ho rucne v triggeru i kdyz to mi neprijde jako moc efektivni. Myslel jsem, ze by to mohla byt pomerne casta uloha a zajimalo me, jestli na to neni nejaka znama finta, at uz v sqline nebo jine DB.

 

 

[s0]

V jiných DB to jde snadno (Oracle, a asi i PostgreSQL), ale sqlite je prostě jednodušší.

Tak to udělej jinak. Udělěj si k TAB ještě třeba TAB_AUDIT a k danému PK tam triggerem budeš házet záznamy. Hehe, třeba se k tomu tak nějak dostaneme...

CREATE TABLE tab
(
x VARCHAR2(64),
y VARCHAR2(64),
unique(x, y) on conflict replace
);

create table tab_audit
(
x VARCHAR2(64),
y VARCHAR2(64),
dml_action varchar2(10),
stamp DATETIME
);

CREATE TRIGGER t_tab_insert
    AFTER INSERT ON tab
    FOR EACH ROW
BEGIN
    insert into tab_audit 
    values (new.x, new.y, 'INSERT', DATETIME('NOW'));
END;

CREATE TRIGGER t_tab_update
    AFTER UPDATE OF x, y ON tab
    FOR EACH ROW
BEGIN
    insert into tab_audit 
    values (new.x, new.y, 'UPDATE', DATETIME('NOW'));
END;

insert into tab values ('1', '1');
insert into tab values ('1', '1');
insert into tab values ('1', '2');
select * from tab;
select * from tab_audit;

update tab set x = '2';
select * from tab;
select * from tab_audit;

Aktuální stav dohledáš (outer) joinem, historii selektem. Jo, a nejsou tam indexy.

Anebo to ošetři v klientský aplikaci jako UPSERT.

[qvaq]

V Oracle jde toto pomoci merge (tedy neni to uz insert) - definuje se co se ma stat pokud zaznam existuje (typicky update) a co pokud neexistuje (typicky insert). Samozrejme triggerem by to slo i v Oracle.

Dovedu si predstavit, ze se takovy trigger muze nekdy hodit, nicmene mam s tim dost spatne zkusenosti. Pokud insert uz neni ve skutecnosti insert, muze to nekdy byt docela matouci, takze tam kde to jde preferuji explicitni reseni (zmineny merge nebo select a podle vysledku insert/update) - kazdemu je pak z kodu jasne, co se vlastne v tabulce stane.

[s0]

já bych triggery nejradši zrušil úplně ;)