NiXXX
Member | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
DROP SCHEMA IF EXISTS test CASCADE;
DROP ROLE IF EXISTS test;
commit;
CREATE USER test WITH
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
INHERIT
LOGIN
NOREPLICATION
NOBYPASSRLS
CONNECTION LIMIT -1
PASSWORD 'qwerty';
CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION test;
CREATE TABLE test.tbl (
tbl_id integer NOT NULL,
tbl_name varchar(255) NOT NULL,
CONSTRAINT tbl_pk PRIMARY KEY (tbl_id)
) WITH (
OIDS=FALSE
);
CREATE TABLE test.tbl_h (
tbl_id integer NOT NULL,
tbl_name varchar(255) NOT NULL,
tbl_h_dt TIMESTAMP
) WITH (
OIDS=FALSE
);
CREATE OR REPLACE FUNCTION tbl_h_fnc ()
RETURNS trigger AS
$$
DECLARE
BEGIN
insert into test.tbl_h select t.tbl_id, t.tbl_name, current_timestamp from test.tbl t;
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER tbl_h_trg
BEFORE UPDATE ON test.tbl
FOR EACH ROW EXECUTE PROCEDURE tbl_h_fnc();
--DROP TRIGGER tbl_h_fnc ON tbl;
--DROP FUNCTION tbl_h_fnc();
ALTER TABLE test.tbl_h ADD CONSTRAINT tbl_h_fk FOREIGN KEY (tbl_id) REFERENCES test.tbl(tbl_id);
select * from test.tbl t;
select * from test.tbl_h th;
insert into test.tbl values (111, 'Test');
update test.tbl set tbl_name = 'BLABLABLA' where tbl_id = 111;
commit;
select * from test.tbl t;
select * from test.tbl_h th;
|
Всего записей: 365 | Зарегистр. 06-05-2003 | Отправлено: 14:47 01-02-2023 | Исправлено: NiXXX, 15:45 01-02-2023 |
|
