Re: How to avoid 2nd trigger

[lenygold via DBMonster.com]

What about using delete/insert instead of update?

Lennart wrote:

>I am currently developing trigger based screen edit fro several 100 screens
in COBOL/CICS

[quoted text clipped - 81 lines]

>I don't have MERGE STATEMENT, and I don't have INSTEAD OF TRIGGERS.
Any Ideas?

I don't think you can avoid that since triggers are fired either after
update or insert. Merge wont do you any good and instead of triggers
only works for views.

Is there some specific reason that you don't want to use check
constraints and foreign keys for this purpose?

/Lennart

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200807/1

[Lennart]

On Jul 14, 1:05 pm, "lenygold via DBMonster.com" <u41482@uwewrot e:

What about using delete/insert instead of update?

I don't follow, are you going to forbid update operations on your
tables? If that is the case already have the insert trigger, so you
shouldn't have to do anything more.

I still don't understand why you are implementing this validation via
triggers instead of declarative constraints.

/Lennart

[...]

[lenygold via DBMonster.com]

How can i use declarative constraints for relarional edits?
For exam[le date of bitrh should always less than date of hire?

Lennart wrote:

>What about using delete/insert instead of update?

I don't follow, are you going to forbid update operations on your
tables? If that is the case already have the insert trigger, so you
shouldn't have to do anything more.

I still don't understand why you are implementing this validation via
triggers instead of declarative constraints.

/Lennart

[...]

--
Message posted via http://www.dbmonster.com

[Lennart]

On Jul 14, 3:28 pm, "lenygold via DBMonster.com" <u41482@uwewrot e:

How can i use declarative constraints for relarional edits?
For exam[le date of bitrh should always less than date of hire?

ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
CHECK ( BIRTHDATE < HIREDATE )

/Lennart

[...]

[lenygold via DBMonster.com]

Thank you Lennart.
I like this approach.
One more question. What if you a manager or superviser then commissions
always should be zero and education level more then 10.

Lennart wrote:

>How can i use declarative constraints for relarional edits?
For exam[le date of bitrh should always less than date of hire?

ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
CHECK ( BIRTHDATE < HIREDATE )

/Lennart

[...]

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200807/1

[Lennart]

On Jul 14, 5:02 pm, "lenygold via DBMonster.com" <u41482@uwewrot e:

Thank you Lennart.
I like this approach.
One more question. What if you a manager or superviser then commissions
always should be zero and education level more then 10.

In general, DB2 does not support sub selects in check constraints, but
as long as the rule regards a relationship between columns in the same
row, it is a good candidate for a check constraint (it is not possible
at all times though). If I understand your table this case should be
something like (I assume supervisor and manager belongs to the JOB
domain) :

ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
CHECK ( NOT ( JOB IN ('manager', 'superviser')) OR ( COMM = 0 AND
EDLEVEL 10 ) )

If you are a manager or supervisor, [ NOT ( JOB IN ('manager',
'superviser')) ] evaluates to FALSE, hence [ (commissions = 0 AND
EDLEVEL 10 ) ] must evaluate to TRUE for the constraint to evaluate
to TRUE.

A number of the attributes in the table are nullable and you have to
be extra careful with these (but that holds whether you put them in a
trigger or in a constraint). For example, what does it mean if COMM is
null, is that ok for a supervisor?
/Lennart

[Lennart]

On Jul 14, 6:32 pm, Lennart <Erik.Lennart.J [e-mail] mwrote:
[...]

ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
CHECK ( NOT ( JOB IN ('manager', 'superviser')) OR ( COMM = 0 AND
EDLEVEL 10 ) )

Perhaps I should have elaborated a bit more about this.

A =B is the same as NOT A OR B. In your case:

JOB IN ('manager', 'superviser') =( COMM = 0 AND EDLEVEL 10 )

can be rewritten as

(NOT JOB IN ('manager', 'superviser')) OR (COMM = 0 AND EDLEVEL 10 )

/Lennart

[lenygold via DBMonster.com]

Thank's again Lennart.
I will test this constrain.
Lennart wrote:

>[...]

>ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
CHECK ( NOT ( JOB IN ('manager', 'superviser')) OR ( COMM = 0 AND
EDLEVEL 10 ) )

Perhaps I should have elaborated a bit more about this.

A =B is the same as NOT A OR B. In your case:

JOB IN ('manager', 'superviser') =( COMM = 0 AND EDLEVEL 10 )

can be rewritten as

(NOT JOB IN ('manager', 'superviser')) OR (COMM = 0 AND EDLEVEL 10 )

/Lennart

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200807/1

[lenygold via DBMonster.com]

Hi again Lennart.
I found this example on our board on 12-22-2003 14:30
is this doable in DB2 OS/390 V8.2

The detail

create table ASSETCLASS
(
ASSETCLASS char(2) not null,
DESCRIPTION char(50),
DEPRECIATIONMET HOD char(35) not null,
USEFULLIFE smallint not null
);

INSERT
INTO AssetClass (AssetClass, Description,Dep reciationMethod ,
UsefulLife )
VALUES ('CS', 'Computer Software', 'Straight Line', 2 );

create table ASSET
(
ASSETID char(6) not null,
ASSETCLASS char(2) not null,
DESCRIPTION char(50) not null,
COST numeric(8,2) not null,
DATEOFPURCHASE date not null,
RESIDUALVALUE numeric(8,2) not null,
check (Cost ResidualValue)
);

INSERT
INTO Asset (AssetID, AssetClass, Description, Cost, DateOfPurchase,
ResidualValue)
VALUES ('CS0003', 'CS', 'MS Office 2000', 2400.00, '2001-11-01',
0.00);

And now the one with the multi-table check constraint

create table DEPRECIATION
(
ASSETID char(6) not null,
DEPRECIATIONDAT E date not null,
DEPRECIABLEAMOU NT numeric(8,2) not null,
check (NOT EXISTS (select b.assetID
from depreciation as a, asset as b, assetClass as c
where a.AssetID = b.AssetID and
b.assetClass = c.AssetClass and
a.depreciationD ate dateadd(year, c.usefulLife,
b.dateOfPurchas e) ))
);

INSERT
INTO Depreciation (AssetId, DepreciationDat e, DepreciableAmou nt)
VALUES ('CS0003', '2004-02-28', 600.00);

Allowed but subsequent inserts fail????? It seems as if my dbms
evaluates the check on the existing table before inserting the new
record???

3 WEEKS LATER:

01-07-2004 01:31
OK i think i now understand how to relate the record/fields to be inserted
with existing data in the db.

So here goes again

create table ASSETCLASS
(
ASSETCLASS char(2) not null,
DESCRIPTION char(50),
DEPRECIATIONMET HOD char(35) not null,
USEFULLIFE smallint not null
);

INSERT
INTO AssetClass (AssetClass, Description,Dep reciationMethod ,
UsefulLife )
VALUES ('CS', 'Computer Software', 'Straight Line', 2 );

INSERT
INTO AssetClass (AssetClass, Description,Dep reciationMethod , UsefulLife )
VALUES ('MV', 'Motor Vehicles', 'Sum of Digits', 5 );

create table ASSET
(
ASSETID char(6) not null,
ASSETCLASS char(2) not null,
DESCRIPTION char(50) not null,
COST numeric(8,2) not null,
DATEOFPURCHASE date not null,
RESIDUALVALUE numeric(8,2) not null,
check (Cost ResidualValue)
);

INSERT
INTO Asset (AssetID, AssetClass, Description, Cost, DateOfPurchase,
ResidualValue)
VALUES ('CS0003', 'CS', 'MS Office 2000', 2400.00, '2001-11-01',
0.00);

INSERT
INTO Asset (AssetID, AssetClass, Description, Cost, DateOfPurchase,
ResidualValue)
VALUES ('MV0005', 'MV', 'LDV HGG702 GP', 30000.00, '1998-06-01', 7500.00);

And now the one with the multi-table check constraint

create table DEPRECIATION
(
ASSETID char(6) not null,
DEPRECIATIONDAT E date not null,
DEPRECIABLEAMOU NT numeric(8,2) not null,
check (NOT EXISTS (select a.assetID
from asset as a, assetClass as b
where AssetID = a.AssetID and
a.assetClass = b.AssetClass and
depreciationDat e >= dateadd(year, b.usefulLife, a.dateOfPurchas e) ))
);

INSERT
INTO Depreciation (AssetId, DepreciationDat e, DepreciableAmou nt)
VALUES ('CS0003', '2003-05-31', 600.00);

works but

INSERT
INTO Depreciation (AssetId, DepreciationDat e, DepreciableAmou nt)
VALUES ('CS0003', '2003-06-07', 600.00);

fails - the usefullLife of MV are used and not that of CS ????

select a.assetID
from asset as a, assetClass as b
where 'CS0003' = a.AssetID and
a.assetClass = b.AssetClass and
<put date here= dateadd(year, b.usefulLife, a.dateOfPurchas e)

only returns assetID for dates >= 2003-11-01 which is what one would expect,
however when used as search condition in check constraint things go wrong.


lenygold wrote:

>Thank's again Lennart.
I will test this constrain.

>>[...]

>>ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>

[quoted text clipped - 12 lines]

>>
/Lennart

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200807/1