Giriş
Söz dizimi şöyle
CREATE TRIGGER Triggerİsmi + "AFTER|BEFORE" + "INSERT|UPDATE|DELETE" + "ON Tabloİsmi" +
"Effect of Trigger" + "Function to Execute"
1. AFTER ve BEFORE Farkı
Açıklaması şöyle. BEFORE ile bir veri tabanı işlemini engelleme şansımız var.
A trigger can run either BEFORE or AFTER an event.If you want to block an event like an INSERT, you will want to run BEFORE. If you need to be sure the event actually is going to occur, AFTER is ideal.
Effect of the Trigger
Açıklaması şöyle. FOR EACH ROW veya FOR EACH STATEMENT değerlerini kullanabiliriz.
A trigger can run either per row, or per statement. Let's say you run a single UPDATE statement that changes 5 rows in a table.If you specify FOR EACH ROW in the trigger, then the trigger will run 5 times. If you specified FOR EACH STATEMENT, then it would only run once.And of course we can't forget the actual code to run when the trigger is activated. In Postgres, is placed in a function and separated from the trigger. Separating the trigger from the code it runs creates cleaner code and allows multiple triggers to execute the same code.
Function to Execute
Açıklaması şöyle.
A trigger function is created like a regular Postgres function, except that it returns a trigger.
Eğer BEFORE işlemi yapılıyorsa açıklaması şöyle
The new keyword represents the values of the row that is to be inserted. It also is the object you can return to allow the insert to continue. Alternatively, when null is returned this stops the insertion.
Function to Execute İçinde Kullanılabilecek Anahtar Kelimeler
1. TG_TABLE_NAME ile değişen tablo ismi elde edilir
2. TG_OP ile işlem tipi (INSERT, UPDATE, DELETE elde edilir
Örnek
Elimizde şöyle bir kod olsun
CREATE TRIGGER audit_log_trigger
BEFORE INSERT OR UPDATE OR DELETE
ON public.case_studies
FOR EACH ROW
EXECUTE FUNCTION audit_trigger();
Şöyle yaparız
CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$ DECLARE new_data jsonb; old_data jsonb; key text; new_values jsonb; old_values jsonb; user_id text; BEGIN user_id := current_setting('audit.user_id', true); IF user_id IS NULL THEN user_id := current_user; END IF; new_values := '{}'; old_values := '{}'; IF TG_OP = 'INSERT' THEN new_data := to_jsonb(NEW); new_values := new_data; ELSIF TG_OP = 'UPDATE' THEN new_data := to_jsonb(NEW); old_data := to_jsonb(OLD); FOR key IN SELECT jsonb_object_keys(new_data) INTERSECT SELECT jsonb_object_keys(old_data) LOOP IF new_data ->> key != old_data ->> key THEN new_values := new_values || jsonb_build_object(key, new_data ->> key); old_values := old_values || jsonb_build_object(key, old_data ->> key); END IF; END LOOP; ELSIF TG_OP = 'DELETE' THEN old_data := to_jsonb(OLD); old_values := old_data; FOR key IN SELECT jsonb_object_keys(old_data) LOOP old_values := old_values || jsonb_build_object(key, old_data ->> key); END LOOP; END IF; IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN INSERT INTO audit_log (table_name, record_id, operation_type, changed_by, original_values, new_values) VALUES (TG_TABLE_NAME, NEW.id, TG_OP, user_id, old_values, new_values); RETURN NEW; ELSE INSERT INTO audit_log (table_name, record_id, operation_type, changed_by, original_values, new_values) VALUES (TG_TABLE_NAME, OLD.id, TG_OP, user_id, old_values, new_values); RETURN OLD; END IF; END; $$ LANGUAGE plpgsql;
1. DECLARE ile trigger içinde kullanılacak yerel değişkenler tanımlanır
2. current_setting() çağrısı ile bir konfigürasyon parametresi okunur. Bu parametre veri tabanına şöyle atanır
SELECT set_config('audit.user_id', 'test user', true);
3. OLD ve NEW eski ve değişen satırları temsil eder. Açıklaması şöyle
In PostgreSQL, OLD represents the row before update while the NEW represents the new row that will be updated (see docs).
Sütunlara erişmek için NEW.id, OLD.id şeklinde kullanırız. Tabii ki sütunun tabloda mevcut olması gerekir.
4. TG_OP ile işlem tipi bulunur. Açıklaması şöyle
TG_OP which is a keyword representing the type of operation (INSERT, UPDATE, or DELETE)
2. AFTER
Örnek - INSERT OR UPDATE OR DELETE
Şöyle yaparız. table_name yerine istediğimiz tablo ismi gelir.
CREATE TRIGGER table_change
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW EXECUTE PROCEDURE notify_change();
Tablo değişince şu metod çağrılır. Burada değişen tablo ismi TG_TABLE_NAME ile elde edilir.
CREATE OR REPLACE FUNCTION notify_change() RETURNS TRIGGER AS $$
BEGIN
SELECT pg_notify('test', TG_TABLE_NAME);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Örnek - UPDATE
Elimizde şöyle bir trigger olsun
CREATE TRIGGER change_time_punch_audit AFTER UPDATE ON time_punchFOR EACH ROW EXECUTE PROCEDURE fn_change_time_punch_audit();
Bu trigger time_punch tablosuna kayıt eklendikten sonra audit tablosuna bir satır ekler. Şöyledir
CREATE OR REPLACE FUNCTION fn_change_time_punch_audit() RETURNS TRIGGER AS $psql$BEGININSERT INTO time_punch_audit (change_time,change_employee_id,time_punch_id,punch_time)VALUES(now(), new.change_employee_id, new.id, old.punch_time);RETURN new;END;$psql$ language plpgsql;
3. BEFORE
Örnek - INSERT
Elimizde şöyle bir tablo olsun
CREATE TABLE employee ( id SERIAL PRIMARY KEY, username VARCHAR );CREATE TABLE time_punch (id SERIAL PRIMARY KEY,employee_id INT NOT NULL REFERENCES employee(id),is_out_punch BOOLEAN NOT NULL DEFAULT FALSE,punch_time TIMESTAMP NOT NULL DEFAULT now());INSERT INTO employee (username) VALUES ('Bear');INSERT INTO time_punch (employee_id, is_out_punch, punch_time)VALUES(1, false, '2020-01-01 10:00:00'),(1, true, '2020-01-01 11:30:00');
Şöyle yaparız
CREATE TRIGGER check_time_punch BEFORE INSERT ON time_punchFOR EACH ROW EXECUTE PROCEDURE fn_check_time_punch();
Trigger'ın çağırdığı metod şöyledir. Burada trigger yeni kayıt out_punch ise bir select çalıştırır ve bunun sonucunda null döner.
CREATE OR REPLACE FUNCTION fn_check_time_punch() RETURNS TRIGGER AS $psql$BEGINIF new.is_out_punch = (SELECT tps.is_out_punchFROM time_punch tpsWHERE tps.employee_id = new.employee_idORDER BY tps.id desc limit 1) THENRETURN NULL;END IF;RETURN new;END;$psql$ language plpgsql;
Örnek
Şöyle yaparız
-- Create a trigger function to update the last_modified timestamp on table updates CREATE OR REPLACE FUNCTION update_last_modified() RETURNS TRIGGER AS $$ BEGIN NEW.last_modified := NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql; -- Create a trigger that uses the function CREATE TRIGGER update_last_modified_trigger BEFORE UPDATE ON your_table FOR EACH ROW EXECUTE FUNCTION update_last_modified();
Örnek - UPDATE
Trigger içinden function çağırmak için şöyle yaparız. Burada OLD ile eski, NEW ile yeni değer elde edilir.
CREATE TRIGGER update_last_edit_date BEFORE UPDATE ON employee
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE PROCEDURE update_last_edit_date();
update_last_edit_date kodu için şöyle yaparız.CREATE FUNCTION update_last_edit_date() RETURNS trigger AS $update_last_edit_date$
BEGIN
NEW.last_edit_date := localtimestamp(0);
RETURN NEW;
END;
$update_last_edit_date$ LANGUAGE plpgsql;
Hiç yorum yok:
Yorum Gönder