× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



On 25-Apr-2016 08:08 -0500, Hoteltravelfundotcom wrote:
How can I know if there is a trigger on a particular file? And can
there be a trigger to limit the number of records as follows:

Item# Rebate date amt
123 050116 100
123 050216 100

Can I have a trigger say that you can only have 2 rebates per item?

For /answers/ to those questions, review\consider the following scripted actions and effects:

create table Trg_Tst
( item_number for Item# numeric(5)
, rebate_date for RebateDat numeric(6)
, rebate_amount for RebateAmt numeric(5)
)
;
create trigger Trg_Tst_BI
before insert on Trg_Tst
referencing new as new
for each row mode db2row
set option dbgview=*source,srtseq=*hex
if ( select count(*)
from Trg_Tst tt
where tt.item# = new.item_number ) >= 2
then signal sqlstate value '7RAMT'
set message_text='"Excessive rebates"';
end if
;
create trigger Trg_Tst_BU
before update of item_number on Trg_Tst
referencing new as new old as old
for each row mode db2row
set option dbgview=*source,srtseq=*hex
when(new.item_number <> old.item_number)
if ( select count(*)
from Trg_Tst tt
where tt.item# = new.item_number ) >= 2
then signal sqlstate value '8RAMT'
set message_text='"Excessive rebates"';
end if
;
insert into Trg_Tst values
( 123, 050116, 100 )
, ( 123, 050216, 100 )
, ( 111, 050316, 100 )
; -- 3 rows inserted
insert into Trg_Tst values
( 123, 050316, 100 )
; -- insert fails with -438 msg SQL0723 "Excessive rebates"
update Trg_Tst
set Item#=123, RebateDat=050316
where RebateDat=050516
; -- update fails with -438 msg SQL0723 "Excessive rebates"
update Trg_Tst set item#=111
where RebateDat=050116
; -- 1 row updated
update Trg_Tst set item#=111
where RebateDat=050216
; -- update fails with -438 msg SQL0723 "Excessive rebates"
-- Note: there could be value from having defined
-- the COUNT aggregate using the INCLUDE clause:
-- INCLUDE--(----aggregate-function-name -(--expression--)
-- in an Encoded Vector Index (EVI); e.g.:
create encoded vector index Trg_Tst_AX
on Trg_Tst (Item#)
INCLUDE(count(*))
;
call qsys2.qcmdexc('DSPFD FILE(TRG_TST) TYPE(*TRG)')
;
select *
from SYSTRIGDEP /* see also SYSTRIGGERS */
where TRIGNAME like 'TRG\_%' escape '\'
or OBJECT_NAME = 'TRG_TST'
;


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.