4.3.2 Использование триггеров для присвоения значений по умолчанию

Столбцам таблицы могут присваиваться значения по умолчанию с по­мощью квалификатора DEFAULT. В качестве таких значений можно за­давать константы или результаты вычисления простых выражений. Если же задание значения по умолчанию требует более сложной логики, необ­ходимо использовать триггер.

В случае нашего примера предположим, что у галереи имеется прави­ло, согласно которому запрашиваемая цена произведения устанавливается равной удвоенной стоимости его приобретения или сумме общей стоимо­сти приобретения и чистой выручки от продажи этого произведения в прошлом. Это правило реализуется с помощью завершающего триггера, показанного в листинге 4.2. Представление, которое используется в рас­сматриваемом триггере, имеет следующий вид:

CREATE VIEW ArtistWorkNet AS
SELECT W.WorkID, Name, Title, Copy, AcquisitionPrice,
SalesPrice, (SalesPrice - AcqisitionPrice) AS NetPrice
FROM TRANSACTION T
JOIN WORK W
ON T.WorkID = W.WorkID
JOIN ARTIST A
ON W.ArtistID = A.ArtistID;

Листинг 4.2.

CREATE OR REPLACE TRIGGER SetAskingPrice BEFORE INSERT ON TRANSACTION
FOR EACH ROW
DECLARE
avgNetPrice numeric(8,2); newPrice numeric(8,2); rowcount integer; BEGIN
SELECT Count(*) INTO rowcount
FROM TRANSACTION
WHERE WorkID = :new.WorkID;
IF rowcount = 0 THEN
:new.AskingPrice := 2*(:new.AcquisitionPrice);
ELSE
SELECT AVG(NetPrice) INTO avgNetPrice FROM ArtistWorkNet AW WHERE AW.WorkID = :new.WorkID GROUP BY AW.WorkID;
newPrice := avgNetPrice + :new.AcquisitionPrice; IF newPrice > 2*(:new.AcquisitionPrice) THEN :new.AskingPrice := newPrice;
ELSE
:new.AskingPrice := 2*(:new.AcquisitionPrice); END IF;
END IF;
END;
/

Триггер сначала подсчитывает количество строк в таблице TRANSACTION, в которых значение WorkID равно :new.WorkID. По­скольку это предваряющий триггер, произведение еще не добавлено в базу данных, и количество будет равным нулю, если это произведение не появ­лялось в галерее ранее. В этом случае :new.AskingPrice устанавливается равным удвоенному значению AcquisitionPrice.

Если произведение появлялось в галерее в прошлом, рассчитывает­ся средняя чистая прибыль от его продажи с помощью представле­ния ArtistWorkNet. После этого вычисляется переменная newPrice как сумма средней чистой прибыли и стоимости приобретения. Наконец, :new.AskingPrice присваивается большее из двух значений — newPrice или удвоенное значение AcquisitionPrice. Так как триггер предваряющий, для усреднения можно использовать встроенную функцию AVG: новая стро­ка еще не добавлена в таблицу WORK, поэтому она не будет учтена при расчете среднего значения.

Следует обратить внимание на один ньюанс: если в какой-либо из строк представления ArtistWorkNet столбец SalesPrice или AcquisitionPrice явля­ется пустым, это может вызвать проблемы при вычислениях в триггере.

Рассмотренный триггер выполняет полезную функцию, избавляя пер­сонал галереи от значительного количества ручной работы, а также повы­шая точность результатов.