Trigger after insert - update rekordu w tabeli

0

Cześć,

Mam dwie tabele:

CREATE TABLE stock_details
(
id bigserial NOT NULL,
closeprice double precision NOT NULL,
date date,
maxprice double precision NOT NULL,
minprice double precision NOT NULL,
openprice double precision NOT NULL,
volume integer NOT NULL,
stock_ticker character varying(255),
CONSTRAINT stock_details_pkey PRIMARY KEY (id),
CONSTRAINT fk_ly62pqfj6inbyk1nbmfy4dobg FOREIGN KEY (stock_ticker)
REFERENCES stocks (ticker) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)

oraz:

CREATE TABLE stocks
(
ticker character varying(255) NOT NULL,
average_volume_10_days double precision,
average_volume_30_days double precision,
volume_ratio double precision,
percent_return double precision,
CONSTRAINT stocks_pkey PRIMARY KEY (ticker)
)

Chciałbym stworzyć triggera after insert dla tabeli stock_details, który działał by w następujący sposób. Gdy zostanie dodany rekord do tabeli stock_details uruchomi się funkcja do obliczenia percent_return w tabeli stocks. Wspomnianą funkcję już sobie napisałem.

CREATE OR REPLACE FUNCTION totalrecords1(param character varying)
  RETURNS double precision AS
$BODY$
declare
	total double precision;
BEGIN
   SELECT (closeprice/openprice-1)*100 into total FROM stock_details where stock_ticker=param order by date DESC;
   RETURN total;
END;

Teraz chciałbym aby połączyć ją z triggerem, i gdy dodam do stock_details wszystkie informacje łącznie z stock_ticker to właśnie policzy mi percent_return na podstawie stock_tickera i ustawi pole percent_return w tabeli stocks jako wynik funkcji. Mam nadzieję, że w miare jasno się wyraziłem.

0

Musisz stworzyć coś na wzór triggera poniżej.
Do zmiennej param przypisujesz ostatni wrzucony stock_ticker z tabeli inserted.stock_ticker(tabela przechowująca wrzucone rekordy, nie musisz jej tworzyć itd) i tutaj uwaga, w SQL SERVER trigger wystrzeliwuje dla każdego polecenia nie dla każdego wiersza.Następnie przypisujesz do zmiennej t wartość którą zwróciła Ci funkcja.Potem już używasz update or insert z wartością zmiennej.Syntax dla SQL SERVER.

create trigger FireNow on stock_details after insert as
declare @t as decimal (10,5);
declare @param varchar(50)=(Select inserted.stock_ticker from inserted )
set @t=dbo.totalrecords1(@param);

--update lub insert..
update stocks
set percent_return=@t

Mam nadzieję, że złapiesz ideę tego co masz zrobić, pozdrawiam ;-)

0

Funkcje masz napisaną źle. Powinna zwracać JEDNĄ wartość. A skoro jedną to dla pewności powinieneś dodać STRICT. No i na pewno wtedy bez ORDER.
Aha. Masz zajebistą nazwę klucza obcego. Powodzenia przy usuwaniu z kodu. :)

0

Udało się ;) Poszedłem jednak w trochę inną stronę. Ostatecznie funkcja trigger dla postgres wygląda tak:

CREATE OR REPLACE FUNCTION percent_return()
RETURNS trigger AS
$BODY$
BEGIN
UPDATE stocks
set percent_return = (NEW.closeprice/NEW.openprice-1)*100
where ticker = NEW.stock_ticker;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION production_default_values_trigger()
OWNER TO postgres;

0

Super, dzięki za info. Poprawione. Jeszcze tylko jedno pytanie jeśli można. Chciałem sobie zrobić triggera before insert:

CREATE OR REPLACE FUNCTION update_average_volume_10_days_trigger()
  RETURNS trigger AS
$BODY$
BEGIN
WITH average_volume_10_days AS
(SELECT volume FROM stock_details WHERE stock_ticker=NEW.stock_ticker ORDER BY date DESC LIMIT 10)

UPDATE stocks
SET average_volume_10_days = avg(volume) FROM average_volume_10_days
WHERE ticker = NEW.stock_ticker;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;
ALTER FUNCTION update_average_volume_10_days_trigger()
  OWNER TO postgres;

Niestety podczas dodawania rekordu do bazy leci error: org.postgresql.util.PSQLException: ERROR: aggregate functions are not allowed in UPDATE. Jak tego uniknąć?

0

Ok nie było pytania. Rozwiązałem problem. Rozwiązanie poniżej:

CREATE OR REPLACE FUNCTION update_average_volume_10_days_trigger()
  RETURNS trigger AS
$BODY$
DECLARE average_volume double precision := (SELECT volume FROM stock_details WHERE stock_ticker=NEW.stock_ticker ORDER BY date DESC LIMIT 10);
BEGIN
UPDATE stocks
SET average_volume_10_days = average_volume
WHERE ticker = NEW.stock_ticker;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;
0

Ale nadal mmasz ten sam błąd z pierwszej funkcji... SELECT ... FROM... LIMIT 10 zwróci od 0 do 10rekordów. Jak masz zamiar wcisnąć to do jednej małej zmiennej?

0

Najmocniej przepraszam ale wczoraj źle wkleiłem.

CREATE OR REPLACE FUNCTION update_average_volume_10_days_trigger()
  RETURNS trigger AS
$BODY$
DECLARE average_volume double precision := (SELECT AVG(volume) from (SELECT volume FROM stock_details WHERE stock_ticker=NEW.stock_ticker ORDER BY date DESC LIMIT 10) as sub);
BEGIN
UPDATE stocks
SET average_volume_10_days = average_volume
WHERE ticker = NEW.stock_ticker;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;

1 użytkowników online, w tym zalogowanych: 0, gości: 1