Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Kavos shopas
- Ateina ateina psichai kurie nori zalios kavos (kava veganams)
- uzpila, parduoda kilogramais, priima uzsakymus, turi kavos aparata (pora rusiu kavos)
- kovos rusis
- pardavimai
- uzsakymai
- CREATE TABLE pardavimai (
- pardavimai_id INTEGER NOT NULL,
- data DATE,
- kavos_rusis_id INT NOT NULL,
- kiekis FLOAT NOT NULL,
- kaina FLOAT NOT NULL,
- PRIMARY KEY (pardavimai_id, kavos_rusis_id)
- );
- INSERT INTO pardavimai (pardavimai_id, data, kavos_rusis_id, kiekis, kaina) VALUES
- (1,'09.10.2018', 1, '1', '1.90'),
- (2,'09.10.2018', 1, '2', '3.80'),
- (3,'09.10.2018', 4, '1', '2.90'),
- (4,'09.10.2018', 4, '1', '2.90'),
- (5,'09.10.2018', 7, '1', '2.50'),
- (6,'09.10.2018', 5, '1', '1.90'),
- (7,'09.10.2018', 4, '1', '2.90'),
- (8,'09.10.2018', 4, '1', '2.90'),
- (9,'09.10.2018', 7, '1', '2.50'),
- (10,'09.10.2018', 1, '2', '3.80'),
- (11,'09.10.2018', 4, '1', '2.90'),
- (12,'09.10.2018', 4, '1', '2.90'),
- (13,'09.10.2018', 7, '1', '2.50'),
- (14,'09.10.2018', 5, '1', '1.90'),
- (15,'09.10.2018', 4, '1', '2.90');
- CREATE TABLE kavos_rusis (
- kavos_rusis_id serial PRIMARY KEY,
- kavos_pavadinimas VARCHAR(40) NOT NULL,
- kg FLOAT NOT NULL,
- kaina FLOAT NOT NULL
- );
- INSERT INTO kavos_rusis (kavos_pavadinimas, kg, kaina) VALUES
- ('Espresso', '0.020', '1.60'),
- ('Espresso', '0.040', '2.40'),
- ('Kapucinas', '0.030', '1.90'),
- ('Kapucinas', '0.050', '2.90'),
- ('Latte', '0.040', '1.90'),
- ('Latte', '0.060', '2.90'),
- ('Chailatte', '0.050', '2.50');
- CREATE TABLE uzsakymai (
- uzsakymai_id INTEGER NOT NULL,
- data DATE,
- padalinys_id INT,
- uzsakymo_pavadinimas VARCHAR(50) NOT NULL,
- gavejas VARCHAR(50) NOT NULL,
- gavimo_data DATE,
- kaina FLOAT,
- PRIMARY KEY (uzsakymai_id, padalinys_id)
- );
- CREATE TABLE padalinys_id (
- padalinys_id serial PRIMARY KEY,
- padalinio_pavadinimas VARCHAR(50)
- );
- INSERT INTO padalinys_id (padalinio_pavadinimas) VALUES ('Akropolis'), ('Europa'), ('GO9');
- CREATE TABLE grazinimas (
- grazinimas_id serial PRIMARY KEY,
- data DATE,
- kavos_rusis_id INT,
- kiekis FLOAT NOT NULL,
- kaina FLOAT NOT NULL
- );
- INSERT INTO grazinimas (data, kavos_rusis_id, kiekis, kaina) VALUES
- ('09.10.2018', '4', '0.050', '2.90');
- CREATE TABLE darbuotojai (
- darbuotojai_id INTEGER,
- vardas VARCHAR(50) NOT NULL,
- pavarde VARCHAR(50) NOT NULL,
- padalinys_id VARCHAR(50),
- pareigos_id VARCHAR(50),
- PRIMARY KEY (darbuotojai_id, padalinys_id, pareigos_id)
- );
- INSERT INTO darbuotojai (vardas, pavarde, padalinys_id, pareigos_id) VALUES
- ('Beata', 'Volodkevic', 1,1),
- ('Snezana', 'Bukovski', 1,1),
- ('Rasa', 'Matinauskaite',1,3);
- INSERT INTO darbuotojai (vardas, pavarde, padalinys_id, pareigos_id)
- VALUES ('Beata', 'Sadovska', 2,1),
- ('Agota Ute', 'Litkeviciute', 2,2),
- ('Evelina', 'Raugaite', 2,3);
- INSERT INTO darbuotojai (vardas, pavarde, padalinys_id, pareigos_id) VALUES
- ('Greta', 'Tugusi',3,1),
- ('Diana', 'Kazakevic', 3,2),
- ('Svetlana', 'Mucin',3,3);
- CREATE TABLE pareigos_id (
- pareigos_id serial PRIMARY KEY,
- pareigos_pavadinimas VARCHAR(50) NOT NULL
- );
- INSERT INTO pareigos_id (pareigos_pavadinimas) VALUES ('pardaveja'), ('vyr.pardaveja'), ('direktore');
- CREATE INDEX darbuotojai_index ON darbuotojai (darbuotojai_id, vardas, pavarde, padalinys_id, pareigos_id);
- CREATE INDEX grazinimas_index ON grazinimas (grazinimas_id, data, kavos_rusis_id, kiekis, kaina);
- Yra kelios lenteles view,
- visos lenteles turi ideksus
- ALTER TABLE grazinimas ADD CONSTRAINT kaina_check CHECK(kaina>0 AND kaina BETWEEN 1 AND 10); - grazinimas -> properties -> constrains -> chech
- Trigeris vartojamas su konkreciais lenteles duomenimus, kai yra keiciamas ar irasomas naujas irasas. Trigerius naudojame kartu su INSERT, UPDATE, DELETE.
- Kada trigeris veikia? - Po, per arba pries INSERT, UPDATE, DELETE sakinio dalimis.
- Kaip kuriami trigeriai?
- Trigeriai kuriami sakiniu CREATE TRIGGER, kuriame nurodoma
- Trigerio kvietimo momentas
- Trigerio kvietimo daþnis
- Trigerio kamienas (aprasomi vienas arba keli sakiniai)
- Trigerio kamieno vykdymo sàlyga
- CREATE TRIGGER Modifikuoti Nr AFTER UPDATE OF Nr ON Vykdytojai REFERENDING OLD AS SeniVykdytojai NEW AS NaujiVikdytojai FOR EACH ROW MODE DB
- CREATE TRIGGER Products_INSERT
- BEFORE UPDATE
- ON Products
- AFTER INSERT
- AS
- INSERT INTO History (ProductId, Operation)
- SELECT Id, 'product added' + ProductName + 'Company' + Manufacturer
- FROM INSERTED;
- *TRIG*
- CREATE TABLE employees(
- id int4 primary key,
- first_name varchar(40) NOT NULL,
- last_name varchar(40) NOT NULL
- );
- CREATE TABLE employee_audits(
- int int4 primary key,
- employee_id int4 NOT NULL,
- last_name varchar(40) NOT NULL,
- changed_on timestamp(6) NOT NULL
- )
- CREATE OR REPLACE FUNCTION log_last_name_changes()
- RETURNS trigger AS
- $BODY$
- BEGIN
- IF NEW.last_name <> OLD.last_name THEN
- INSERT INTO employee_audits(employee_id,last_name,changed_on)
- VALUES(OLD.id,OLD.last_name,now());
- END IF;
- RETURN NEW;
- END;
- $BODY$
- LANGUAGE plpgsql;
- CREATE TRIGGER last_name_changes
- BEFORE UPDATE
- ON employees
- FOR EACH ROW
- EXECUTE PROCEDURE log_last_name_changes();
- CREATE OR REPLACE FUNCTION befo_insert() RETURNS trigger
- AS $$
- BEGIN
- NEW.FIRST_NAME = LTRIM(NEW.FIRST_NAME);
- NEW.LAST_NAME = LTRIM(NEW.LAST_NAME);
- NEW.JOB_ID = UPPER(NEW.JOB_ID); RETURN NEW;
- END; $$
- LANGUAGE plpgsql;
- CREATE FUNCTION trigger_function() RETURN trigger AS
- CREATE TABLE darbuotoju_auditas (
- id int4 PRIMARY KEY,
- darbuotojo_id int4 NOT NULL,
- last_name VARCHAR(40) NOT NULL,
- changed_on timestamp(6) NOT NULL
- )
- CREATE TABLE darbuotojai_audits (
- id int4 primary key,
- darbutojai_id int4 NOT NULL,
- vardas VARCHAR(40) NOT NULL,
- changed_on timestamp(6) NOT NULL
- )
- CREATE OR REPLACE FUNCTION log_last_name_changes()
- RETURNS trigger AS
- $BODY$
- BEGIN
- IF NEW.vardas <> OLD.vardas THEN
- INSERT INTO darbuotojai_audits(darbuotojai_id, vardas, changed_on)
- VALUES(OLD.darbuotojai_id, OLD.vardas, now());
- END IF;
- RETURN NEW;
- END;
- $BODY$
- LANGUAGE plpgsql;
- CREATE TRIGGER last_name_changes
- BEFORE UPDATE
- ON darbuotojai
- FOR EACH ROW
- EXECUTE PROCEDURE log_last_name_changes();
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement