Richard,

At 10:35 AM 20/12/2003 +0100, you wrote:

>Hi to all,...
>
>Im using this trigger, but I would like a change in it...
>
>If he is doing the update instead of the insert I would like that he DON'T
>insert the initial insert statement that triggerd this trigger before
>insert.
>
>
>AS
>BEGIN
> if (EXISTS ( SELECT * FROM lensgroupsales
> WHERE optician_ID = new.optician_ID
> AND lensmaterialgroup_id = new.lensmaterialgroup_id
> AND lenstypegroup_id = new.lenstypegroup_id
> AND sales_date = new.sales_date)) then
> begin
> update lensgroupsales SET amount_sales = amount_sales +
>new.amount_sales, qty_sales = qty_sales + new.qty_sales
> WHERE optician_ID = new.optician_ID
> AND lensmaterialgroup_id =
>new.lensmaterialgroup_id
> AND lenstypegroup_id = new.lenstypegroup_id
> AND sales_date = new.sales_date;
> end
>
>END
>
>Hopping that my question is clear,....thnx

If this trigger belongs to the table lensgroupsales then this is not the
right way to do this task. Use a BI trigger to modify the data going into
a new row, don't use it to try to do conditional DML. The only way to
prevent the INSERT happening is to raise an exception - but of course your
other code won't execute.

Instead, drop the trigger and write the code you want to execute into a
stored procedure. Then, always call the SP instead of passing an INSERT
statement. Something like the following:

create procedure InsOrUpdateLGS (
optician_ID integer,
lensmaterialgroup_id integer,
lenstypegroup_id integer,
amount_sales numeric(15,2),
qty_sales integer,
sales_date timestamp)
as
begin
if (EXISTS ( SELECT * FROM lensgroupsales
WHERE optician_ID = :optician_ID
AND lensmaterialgroup_id = :lensmaterialgroup_id
AND lenstypegroup_id = :lenstypegroup_id
AND sales_date = :sales_date)) then
begin
update lensgroupsales
SET amount_sales = amount_sales + :amount_sales,
qty_sales = qty_sales + :qty_sales
WHERE optician_ID = :optician_ID
AND lensmaterialgroup_id = :lensmaterialgroup_id
AND lenstypegroup_id = :lenstypegroup_id
AND sales_date = :sales_date;
end
else
begin
insert into lensgroupsales ( optician_ID, lensmaterialgroup_id,
lenstypegroup_id, qty_sales, amount_sales, sales_date)
values ( :optician_ID, :lensmaterialgroup_id,
:lenstypegroup_id, :qty_sales, :amount_sales, :sales_date);
end
end

/heLen