Hi Sql gurus,

I want to main maintain stock availability using a view, e.g :
Create View Stock_Availability As
Select Agency,SparePart,
QtyIn = (Select ISNULL(SUM(Qty),0) From T59Invd T59
Where T59.Agency=M64.Agency and
T59.SparePart = M64.SparePart),
QtyOut = (Select ISNULL(SUM(Qty),0) From T61Sald T61
Where T61.Agency=M64.Agency and
T61.SparePart = M64.SparePart)
From m64part M64
/*T59Invd and T61Sald are my transaction tables.*/

Is it possible to implement a 'CHECK CONSTRAINT' here to make sure that
QtyIn - QtyOut >= 0 ?
I try to avoid using Instead Of Trigger on all Transaction tables and
prefer a centralized checking point like a 'constraint', how can I ?

Thanks a lot for your help,
Krist









*** Sent via Developersdex http://www.developersdex.com ***

Without any DDL or more specs, I am guessing that M64 has all the parts,
and that T59 and T61 may or may not reference them. That could mean
that the expensive scalar subqueries can be replaced by outer joins,
like this:

CREATE VIEW Stock_Availability (agency, sparepart, qty_in, qty_out)
AS
SELECT M64.agency, M64.sparepart,
COALESCE (SUM(T59.qty), 0),
COALESCE (SUM(T61.qty), 0)
FROM M64part AS M64
LEFT OUTER JOIN
T59invd AS T59
ON T59.agency = M64.agency
AND T59.sparepart = M64.sparepart
LEFT OUTER JOIN
T61sald AS T61
ON T61.agency = M64.agency
AND T61.sparepart = M64.sparepart
GROUP BY agency, sparepart
HAVING COALESCE (SUM(T59.qty), 0)
[quoted text, click to view]
that (QtyIn - QtyOut >= 0)? I am trying to avoid using an INSTEAD OF
Trigger on all Transaction tables and prefer a centralized checking
point like a 'constraint', how can I? <<

Nope. In Full SQL-92, you could use a CREATE ASSERTION statement, but
this does not yet exist in SQL Server. Since this VIEW is not
updatable, you cannot use a WITH CHECK OPTION. The INSTEAD OF trigger
is the best way to go in this case; this is what it was meant to do.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

You can't create a constraint on a view. You probably have to use a trigger
instead. Or a constraint on a base table, but that might not be easy,
depending on your logic.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp


[quoted text, click to view]

hi krist,

try any of the following option.

--you can create same view using following definition.

select agency,sparepart,QtyIn,QtyOut
from
(Select Agency,SparePart,
QtyIn = (Select ISNULL(SUM(Qty),0) From T59Invd T59
Where T59.Agency=M64.Agency and
T59.SparePart = M64.SparePart),
QtyOut = (Select ISNULL(SUM(Qty),0) From T61Sald T61
Where T61.Agency=M64.Agency and
T61.SparePart = M64.SparePart)
From m64part M64) X
where QtyIn - QtyOut >= 0

--or
--run a query against view as

select * from Stock_Availability
where QtyIn - QtyOut >= 0


--
Vishal Parkar
vgparkar@yahoo.co.in

in this case you will have to write triggers on base tables. your view will not be modifiable,
also you can not implement these kind of checks using views.

See following example which will create trigger on t59invd table, you can use similar logic to
create trigger on t61said table as well.

use tempdb
go
create table t59invd(qty int, agency int, sparepart int)
create table T61Sald(qty int, agency int, sparepart int)
create table m64part(agency int, sparepart int)
go
create trigger tr_t59invd on t59invd
for update,delete as
if @@rowcount = 0
return
if
(
select (Select ISNULL(SUM(t59.Qty),0)
From T59Invd T59 , m64part m64, deleted X
Where T59.Agency=M64.Agency and
T59.SparePart = M64.SparePart and
x.agency = t59.agency and x.sparepart = t59.sparepart) -
(Select ISNULL(SUM(t61.Qty),0) From T61Sald T61 , m64part m64, deleted X
Where T61.Agency=M64.Agency and
T61.SparePart = M64.SparePart and
x.agency = t61.agency and x.sparepart = t61.sparepart)) <= 0
begin
raiserror ('qty below 0' , 16,1)
rollback
end
go
--sample records
insert into m64part values(1,1)
insert into m64part values(1,2)

insert into t59invd values(5, 1,2)
insert into t59invd values(6, 1,2)

--test
delete from t59invd where sparepart = 2 and qty = 5 --success balance wont go below or equal
to 0
delete from t59invd where sparepart = 2 --error balance is 0


--
Vishal Parkar
vgparkar@yahoo.co.in

Hi,
Thanks for the reply.

By using the method you mention, how can I get the 'Constraint' behaviour of
the view ?
What I look for is :
Anytime and anywhere any transactions involved in the view (in the example
: T59Invd and T61SalD, actually there are more..) is Inserted, Updated,
Deleted and if QtyIn - QtyOut is < 0, one 'constraint' is violated, error
raised.

Is this possible ?

Thanks,
Krist


[quoted text, click to view]