CASE statement in a JOIN

Hi,
I have two tables TABLE_A and TABLE_B

TABLE_A has rows like this:
PROJECT_ID TASK_ID TASK_NAME
1 100 One Hundred
1 110 One Hundred Ten
1 120 One Hundred Twenty
2 200 Two Hundred
3 300 Three Hundred
3 310 Three Hundred Ten

TABLE_B has rows like this:
PROJECT_ID TASK_ID AMOUNT
1 100 1000
1 110 1100
2 NULL 2000
3 300 3000

I want to inner join TABLE_A and TABLE_B such that if TASK_ID is available in TABLE_B, then join should happen on TASK_ID (on TABLE_A.TASK_ID=TABLE_B.TASK_ID), if TASK_ID is not available the join should happen on PROJECT_ID.

For example for PROJECT_ID=2, there is no TASK_ID in TABLE_B (in this situation the join should be ON PROJECT_ID)

How can we do a CASE like situation here?

Thanks in advance
qADoes this work?

ON ft1.field1 = Coalesce(t2.field1)
OR ft1.field2 = Coalesce(t2.field2)|||I have never seen coalesce used with 1 argument.|||Oops... I have forgotten where I was coming from with that now...

Remove the coalesces and it might work..?
(*confused*)|||You can do it the way that georgev mentioned (without the coalesces), but reqardless of how you do this, you are almost surely going to have duplication in your result set. Any place that you have more than one record in TABLE_A with the same PROJECT_ID and record(s) in TABLE_B with that PROJECT_ID and a null TASK_ID, you are going to have duplication of the values in TABLE_B.|||I agree. If I use OR clause here, it would give lots of duplicates. Any other suggestions??|||on (TABLE_A.TASK_ID=TABLE_B.TASK_ID or ( (TABLE_A.TASK_ID<>TABLE_B.TASK_ID or TABLE_B.TASK_ID is null) and
TABLE_A.PROJECT_ID=TABLE_B.PROJECT_ID)
)
?|||look ma, no dupes:select TABLE_A.PROJECT_ID
, TABLE_A.TASK_ID
, TABLE_A.TASK_NAME
, TABLE_B.PROJECT_ID
, TABLE_B.TASK_ID
, TABLE_B.AMOUNT
from TABLE_A
inner
join TABLE_B
on TABLE_B.TASK_ID = TABLE_A.TASK_ID
union
select TABLE_A.PROJECT_ID
, TABLE_A.TASK_ID
, TABLE_A.TASK_NAME
, TABLE_B.PROJECT_ID
, TABLE_B.TASK_ID
, TABLE_B.AMOUNT
from TABLE_A
inner
join TABLE_B
on TABLE_B.PROJECT_ID = TABLE_A.PROJECT_ID
where not exists
( select 937
from TABLE_B
where TASK_ID = TABLE_A.TASK_ID )|||select TABLE_A.PROJECT_ID
, TABLE_A.TASK_ID
, TABLE_A.TASK_NAME
, TABLE_B.PROJECT_ID
, TABLE_B.TASK_ID
, TABLE_B.AMOUNT
from TABLE_A
inner
join TABLE_B
on TABLE_B.TASK_ID = TABLE_A.TASK_ID
union
select TABLE_A.PROJECT_ID
, TABLE_A.TASK_ID
, TABLE_A.TASK_NAME
, TABLE_B.PROJECT_ID
, TABLE_B.TASK_ID
, TABLE_B.AMOUNT
from TABLE_A
inner
join TABLE_B
on TABLE_B.PROJECT_ID = TABLE_A.PROJECT_ID
where not exists
( select *
from TABLE_B
where TASK_ID = TABLE_A.TASK_ID )|||Is it me, or is that identical to r937's post, except you changed the 937 to an asterix..?|||i love it when my answers are secretly adopted by people :)

by the way, it's "asterisk" -- asterix (http://en.wikipedia.org/wiki/Asterix) was a french comic book character

:)|||That might be why I spell it that way then ;)
Would I be correct in assuming that

IF EXISTS(SELECT 1 FROM MyTable)

Is more efficient than

IF EXISTS(SELECT * FROM MyTable)

Assuming that there is more than one column in the table?|||SELECT with EXISTS was optimized in SQL 2000 (I believe). It does not really pull back all of the columns as was the case I believe in SQL 7. I stopped eradicating it from code a while ago.|||Well a large portion of my work is still on 6.5 :p
I assumed that it would only ever need to return a single row - but since 6.5 doesn't have the TOP function, I assumed it returned the entire resultset.

Lots of assumptions, eh?|||I believe exists exits once it finds a nonqualifying row while NOT EXISTS will scan the whole result set.|||It seems so simple now! :p|||...while NOT EXISTS will scan the whole result set.actually, in this case it would likely be an index seek (or whatever it's called)

:)|||The reason that I stated that you will likely have duplicates regardless is because even in R937's code you could have duplicates in certain cases. While in your specific example you wouldn't have any duplicates with that code, you might have duplicates if there are any cases where you have any records on TABLE_B with a null TASK_ID and a PROJECT_ID that matches multiple records in TABLE_A or if you have multiple records in TABLE_B with a null TASK_ID. PROJECT_ID is evidently not a primary key in TABLE_A, whereas from your example, TASK_ID looks like it might be. For instance take the example below:

TABLE_A has rows like this:
PROJECT_ID TASK_ID TASK_NAME
1 100 One Hundred
1 110 One Hundred Ten
1 120 One Hundred Twenty
2 200 Two Hundred
3 300 Three Hundred
3 310 Three Hundred Ten
3 320 Three Hundred Twenty

TABLE_B has rows like this:
PROJECT_ID TASK_ID AMOUNT
1 100 1000
1 110 1100
2 NULL 2000
3 300 3000
3 NULL 4000
3 NULL 4100

Your result set will be:
PROJECT_ID TASK_ID TASK_NAME PROJECT_ID AMOUNT
1 100 One Hundred 100 1000
1 110 One Hundred Ten 110 1100
2 200 Two Hundred 200 2000
3 300 Three Hundred 300 3000
3 310 Three Hundred Ten NULL 4000
3 320 Three Hundred Twenty NULL 4000
3 310 Three Hundred Ten NULL 4100
3 320 Three Hundred Twenty NULL 4100

If you don't have any of that type of issue and are certain that you won't in the future, or you actually want that type of result in that type of instance, then no worries. Otherwise you might have a problem.

CASE statement advice needed

It appears that this query goes into an endless loop. Can someone please identify the error? The 4 tables referenced (CPADF_DT , CPACCDEF , CPARTY, CPDELACC) have less that 2000 records each.

--CP BANK ACCOUNT DEFAULTS
SELECT
CPARTY.NAME AS "COUNTERPARTY" ,
CPACCDEF.TRANS_TYPE AS "TRANSACTION TYPE",
CPACCDEF.SECTYPE AS "INSTRUMENT TYPE",
CPACCDEF.ENTITY AS "ENTITY",
CPACCDEF.FACILITY AS "RELATIONSHIP",
CPACCDEF.CFLOW_TYPE AS "CASHFLOW TYPE",
CPACCDEF.CCY AS "CURRENCY",
CASE CPACCDEF.PAY_REC
WHEN 'B' THEN 'BOTH'
WHEN 'P' THEN 'PAYMENTS'
WHEN 'R' THEN 'RECEIPTS'
ELSE
CPACCDEF.PAY_REC END AS "PAYMENTS/RECEIPTS",
CPADF_DT.EFFECT_DT AS "EFFECTIVE DATE",
CPADF_DT.ACC_NO AS "ACCOUNT NUMBER / IDENTIFIER",
CPDELACC.ACC_NAME AS "ACCOUNT NAME/PAYMENT METHOD", CPDELACC.CCY AS "CURRENCY",
CPDELACC.BANK_NAME AS "BANK NAME"
FROM CPADF_DT , CPACCDEF , CPARTY, CPDELACCHey first of all the join happening here is cross join and hence it will result in the cartesian product of all the 4 tables and I don't think it is the intended behavior.

there should be some joining condition between the tables.

finally it is recommended to use aliases for the tables in the join so that query looks good :)|||Hi,

I think its not going into an endless loop. The thing is that, since no join condition is provided it will be a cartesian product and if there are lot of rows in each of the tables it will take a lot of time to execute the above query.

The solution to ur problem is that have join conditions so that you filter out unwanted rows.sql

Case Insensitivity is on server wide: tables render case sensative...

Hello:

I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?

Any suggestions?Have a look at "Using SQL Collations" in SQL Server Books Online|||Hello:

I went ahead and took a look inside books online and pretty much found things that I already know. My current collation is: SQL_Latin1_General_CP1_CI_AI.. So the server is actually CaSe insensative... But some of my tables still act like case sensitivity is turned on in most areas where I have not done anything on my part to cause this behavior. So I am guessing that since the tables were moved from a CS_AS to a CI_AI, the tables caused were rendered case sensative although it is not in a CS_AS enviornment... My question is that would I need to rebuild the database manually from scratch or is their an easier way to do this? Thank you.|||If the AS collations appear only on a few tables you can alter those tables only - something like:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_819v.asp|||Hello:

Yes I followed that link it was more helpful... looks like a daunting manual task.. thank you

Case in Inner Join? SQL statement help!!

Hi,

I have a 2 different telephone number tables,

Table 1 has some numbers beginning with '0' i.e. 08001234567 and some without the '0' i.e 8001234567

on my Table2 i only have numbers starting with '0' i.e 08001234567.

I would like to make a INNER JOIN statement and check if the telephone number dont have a starting '0', then append to it and try to do the join so I get both set of data.

for example (this doesnt work however...):

SELECT *

FROM dbo.Calls INNER JOIN
ON

CASE WHEN SUBSTRING(dbo.CallData.TelephoneNumber, 0, 1) = '0'

THEN dbo.Calls.TelephoneNumber = dbo.Post.TelephoneNumber

ELSE '0' & dbo.Calls.TelephoneNumber = dbo.Post.TelephoneNumber <--append a 0 at the start

END CASE

AND dbo.Products.FK_Client = dbo.CallDataSets.FK_Client
GROUP BY dbo.CallDataSets.FK_Client

I hope you understand what I am trying to achieve here...

Any help is appreciated!

Many thanks,

Jon

Try something like this:

Code Snippet

SELECT *
FROM dbo.Calls c
JOIN ...
ON ...
JOIN Post p
ON ( right(( '0' + c.TelephoneNumber ), 11 )) = ( right(( '0' + p.TelephoneNumber ), 11 ))
JOIN Products pr
ON ...

You only need to add the leading zero on the table that requires it -both are used above for illustration.

|||

thanks for the reply.

but the reason i need the CASE STATEMENT is that some of them don't have Zeros, and it wont join the table.

I want both numbers to be join with.

many thanks.

|||

So, did you even try the suggestion I sent.

If you did, what happened?

I have a 2 different telephone number tables,

Table 1 has some numbers beginning with '0' i.e. 08001234567 and some without the '0' i.e 8001234567

on my Table2 i only have numbers starting with '0' i.e 08001234567.

And of course, you could choose to correct the data that doesn't have a leading zero with an UPDATE statement like this (then both tables would have data in the same form):

Code Snippet

UPDATE Table1
SET TelephoneNumber = ( '0' + TelephoneNumber )
WHERE len( TelephoneNumber ) = 10

(or)

...
WHERE left( TelephoneNumber, 1 ) <> '0'

|||

thanks!

in the end is use CAST(object as bigint) to get rid or the zero.

|||That would also work just as well.

Case & Nested table selection and errors

hi

Here are the two tables again.

1)PATIENT(PATIENT_ID,NAME,CITY)

2) DISEASES(DISEASE_ID,NAME)

I am trying to select patient table as case and diseases table as nested to create an association model. i m getting following error.

Disease table cannot be used as a nested table because it does not have a many-to-one relationship with the case table. You need to create a many-to-one relationship between the two tables in the data source file.

i have created a relationship by dragging Disease_id from diseases table on Patient_id in patient table. when i am trying to select Patient_id as key, City as input, it is not showing disease_id to choose as a predict column.

please suggest me if i am doing anything wrong? i have not done any thing to do my datbase, just selected the tables i want to create an association model on and trying to create association model.

your help and insight is highly appreciated.

regards

Raju

For nested table modeling, you need a one to many relationship between the patient table and the disease table. Such a relationship would relate, for instance, a patient with the diseases he suffers. Your table would typically look like this:

1) PATIENT (PATIENT_ID, NAME, CITY)

2) DISEASES (PATIENT_ID, DISEASE_ID)

In this set of tables, you can define the relationship linking Patient.Patient_ID to Diseases.Patient_ID (assuming that one or more diseases were reported for most of the patients)

In the set of tables you mentioned above, assuming that Patient.Name is the patient's name and Diseases.Name is the name of the disease, it is not possible to have such a relationship between the tables. However, in most normalized DB architectures, your tables suggest that there must be another table, linking patients to diseases. That one should be modeled as nested.

Once you get this problem solved, it is possible during mining to use the actual disease name ( by setting the Name binding for the nested Disease_id column). If you have troubles setting this binding, please post a question here

Regards

|||

Thank you!

Sorry! i didn't mention about third table patient_diseases(patient_id, Disease_id). There is thrid table with foreign keys table for patient and diseases tables.

what table should i select as case and what table as nested? After that what colums should i select from which tables as Key, input, predict? could you let me setting the Name binding for the nested Disease_id column?

Your help is appreciated.

regards

raju

|||Please see this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=690139&SiteID=1&mode=1

Cascading updates question

I have two tables, Stock and Positions. Stock contains a Symbol column and a
Price column, and Symbol is the key. Positions contain the columns as well a
s
several other columns whose data should change with the Price. Positions can
have the same Symbol multiple times (it is keyed by Symbol-Account).
My design is to have a foreign key between the tables, so that when the
Price in the Stock column is updated, the Price in Positions also updates. I
am making some columns in the Positions table computed columns, so that they
recalculate when the Price updates.
My questions/concerns are:
1. Is this design any good? One alternative I was thinkning of was to use no
computed columns, and run a stored procedure frequently to update the
Positions.
2. Will the computed columns updated automatically when Price changes?
3. Will there be the possibility that some rows in Positions with the same
symbols are not updated simultaneously, so that the Price could be different
for the same symbol?
4. Do the rows lock while they are updating? This has implications because I
am querying this table often for other purposes.
5. If one computed column depends on another computed column, is there a way
to specify the order in which they calculate, or is this just a big no-no?
I am grateful for any insight.
Thank you,
CP Developer"CP Developer" <steved@.newsgroup.nospam> wrote in message
news:A40102EA-6AF1-4377-9332-09EFCAA83204@.microsoft.com...
>I have two tables, Stock and Positions. Stock contains a Symbol column and
>a
> Price column, and Symbol is the key. Positions contain the columns as well
> as
> several other columns whose data should change with the Price. Positions
> can
> have the same Symbol multiple times (it is keyed by Symbol-Account).
> My design is to have a foreign key between the tables, so that when the
> Price in the Stock column is updated, the Price in Positions also updates.
> I
> am making some columns in the Positions table computed columns, so that
> they
> recalculate when the Price updates.
> My questions/concerns are:
> 1. Is this design any good? One alternative I was thinkning of was to use
> no
> computed columns, and run a stored procedure frequently to update the
> Positions.
There are a few reasonable ways I can think of to have a comupted column
based on a column in a related table.
Put a phoney foreign key on (StockID, Price) and use cascade updates (your
idea).
Put a trigger on Stock to update the related positions.
Use a view to join the two tables and define the calculations there.

> 2. Will the computed columns updated automatically when Price changes?

> 3. Will there be the possibility that some rows in Positions with the same
> symbols are not updated simultaneously, so that the Price could be
> different
> for the same symbol?
No.

> 4. Do the rows lock while they are updating? This has implications because
> I
> am querying this table often for other purposes.
Yes. Make sure you have an index supporting the foreign key relationship.

> 5. If one computed column depends on another computed column, is there a
> way
> to specify the order in which they calculate, or is this just a big no-no?
>
No you cannot base one computed column on another. However you are free to
cut and paste the calculation for one column into the other.
Here's an example:
drop table position
drop table stock
go
create table stock
(
id int primary key,
price decimal(9,2),
constraint uk_id_price
unique (id,price)
)
create table position
(
account int not null, -- references account
stock int not null references stock,
price decimal(9,2) not null,
other_price as cast(price*.9 as decimal(9,2)),
constraint pk_position
primary key(account,stock),
constraint fk_position_stock_price
foreign key (stock,price)
references stock(id,price)
on update cascade
)
create index ix_position_stock_price
on position(stock,price)
go
insert into stock(id,price) values (1,3.50)
insert into position(account,stock,price) values (23,1,3.50)
go
update stock set price = 5.25 where id = 1
select * from position
David|||The columns will update when the price changes.
Create Table TableA
(
Symbol varchar(10),
Price Money,
Qty Int,
TotalCost As (Price * Qty)
)
Insert Into TableA
Select 'GBP', 14.52, 2
select * From TableA
Update TableA
Set Qty = 151, price = 45.65
select * From TableA
Drop table TableA
HTH
Barry|||I would store the price in one table only, and select it from there. If
you need you selects to be as fast as possible, consider using an
indexed view.

Cascading Updates / Delete Problem

I have 3 tables set up in the typical customer - order - order item setup. I
have cascading updates & deletes on the customer and order tables. Primary &
secondary keys are correct.
When I delete, or update the customer table key field, it takes about 60
seconds to cascade to the child tables. If I execute manually the delete
operation startting at the order item table, then the orders, and finally the
customer table it only takes a second.
Why is the cascading taking so long, when doing the same task manually takes
a fraction of the time?If you do the manual delete, do you have the cascading foreign leys in place
or not? If you don't, you can probably fix the issue by creating indexes on
the foreign key columns.
--
Jacco Schalkwijk
SQL Server MVP
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>I have 3 tables set up in the typical customer - order - order item setup.
>I
> have cascading updates & deletes on the customer and order tables. Primary
> &
> secondary keys are correct.
> When I delete, or update the customer table key field, it takes about 60
> seconds to cascade to the child tables. If I execute manually the delete
> operation startting at the order item table, then the orders, and finally
> the
> customer table it only takes a second.
> Why is the cascading taking so long, when doing the same task manually
> takes
> a fraction of the time?|||All keys are in place.
"Jacco Schalkwijk" wrote:
> If you do the manual delete, do you have the cascading foreign leys in place
> or not? If you don't, you can probably fix the issue by creating indexes on
> the foreign key columns.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
> >I have 3 tables set up in the typical customer - order - order item setup.
> >I
> > have cascading updates & deletes on the customer and order tables. Primary
> > &
> > secondary keys are correct.
> > When I delete, or update the customer table key field, it takes about 60
> > seconds to cascade to the child tables. If I execute manually the delete
> > operation startting at the order item table, then the orders, and finally
> > the
> > customer table it only takes a second.
> > Why is the cascading taking so long, when doing the same task manually
> > takes
> > a fraction of the time?
>
>|||"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
>
Then you'll need to post a repro.
David|||What about indexes on the foreign key columns?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
> "Jacco Schalkwijk" wrote:
>> If you do the manual delete, do you have the cascading foreign leys in place
>> or not? If you don't, you can probably fix the issue by creating indexes on
>> the foreign key columns.
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
>> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>> >I have 3 tables set up in the typical customer - order - order item setup.
>> >I
>> > have cascading updates & deletes on the customer and order tables. Primary
>> > &
>> > secondary keys are correct.
>> > When I delete, or update the customer table key field, it takes about 60
>> > seconds to cascade to the child tables. If I execute manually the delete
>> > operation startting at the order item table, then the orders, and finally
>> > the
>> > customer table it only takes a second.
>> > Why is the cascading taking so long, when doing the same task manually
>> > takes
>> > a fraction of the time?
>>

Cascading Updates / Delete Problem

I have 3 tables set up in the typical customer - order - order item setup. I
have cascading updates & deletes on the customer and order tables. Primary &
secondary keys are correct.
When I delete, or update the customer table key field, it takes about 60
seconds to cascade to the child tables. If I execute manually the delete
operation startting at the order item table, then the orders, and finally the
customer table it only takes a second.
Why is the cascading taking so long, when doing the same task manually takes
a fraction of the time?
If you do the manual delete, do you have the cascading foreign leys in place
or not? If you don't, you can probably fix the issue by creating indexes on
the foreign key columns.
Jacco Schalkwijk
SQL Server MVP
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>I have 3 tables set up in the typical customer - order - order item setup.
>I
> have cascading updates & deletes on the customer and order tables. Primary
> &
> secondary keys are correct.
> When I delete, or update the customer table key field, it takes about 60
> seconds to cascade to the child tables. If I execute manually the delete
> operation startting at the order item table, then the orders, and finally
> the
> customer table it only takes a second.
> Why is the cascading taking so long, when doing the same task manually
> takes
> a fraction of the time?
|||All keys are in place.
"Jacco Schalkwijk" wrote:

> If you do the manual delete, do you have the cascading foreign leys in place
> or not? If you don't, you can probably fix the issue by creating indexes on
> the foreign key columns.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>
>
|||"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
>
Then you'll need to post a repro.
David
|||What about indexes on the foreign key columns?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...[vbcol=seagreen]
> All keys are in place.
> "Jacco Schalkwijk" wrote:

Cascading Updates / Delete Problem

I have 3 tables set up in the typical customer - order - order item setup. I
have cascading updates & deletes on the customer and order tables. Primary &
secondary keys are correct.
When I delete, or update the customer table key field, it takes about 60
seconds to cascade to the child tables. If I execute manually the delete
operation startting at the order item table, then the orders, and finally th
e
customer table it only takes a second.
Why is the cascading taking so long, when doing the same task manually takes
a fraction of the time?If you do the manual delete, do you have the cascading foreign leys in place
or not? If you don't, you can probably fix the issue by creating indexes on
the foreign key columns.
Jacco Schalkwijk
SQL Server MVP
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>I have 3 tables set up in the typical customer - order - order item setup.
>I
> have cascading updates & deletes on the customer and order tables. Primary
> &
> secondary keys are correct.
> When I delete, or update the customer table key field, it takes about 60
> seconds to cascade to the child tables. If I execute manually the delete
> operation startting at the order item table, then the orders, and finally
> the
> customer table it only takes a second.
> Why is the cascading taking so long, when doing the same task manually
> takes
> a fraction of the time?|||All keys are in place.
"Jacco Schalkwijk" wrote:

> If you do the manual delete, do you have the cascading foreign leys in pla
ce
> or not? If you don't, you can probably fix the issue by creating indexes o
n
> the foreign key columns.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:AEEEB81C-C1F2-4AD1-B2EE-39E4B8C88F32@.microsoft.com...
>
>|||"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...
> All keys are in place.
>
Then you'll need to post a repro.
David|||What about indexes on the foreign key columns?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:232641B7-91F9-439E-83EE-3ECB623432F8@.microsoft.com...[vbcol=seagreen]
> All keys are in place.
> "Jacco Schalkwijk" wrote:
>

Cascading Triggers

Hi,
I have 3 Tables with After Update Triggers.
They all cascade like City -> Province -> State (as a simplified example)
If I Update a bit (A) in City, a Trigger Sets a bit (B) in Province.
If I Update the bit B in Province, a Trigger Sets a Bit (C) in State.
This works.
However:
If I Set A, a trigger on table City sets B BUT a trigger on table Province
DOESN'T in turn Set C in table State.
So: Individually the Triggers work, but they don't Cascade their action.
Any advise?
TIA,
MichaelHi Michael,
I think I'm having the same problem. I'm hoping to take action via a
trigger on child records when the parent is deleted, but the child table's
trigger doesn't seem to fire. Hopefully someone has a suggestion.
Rgds,
Bill
"Michael Maes" <michael@.merlot.com> wrote in message
news:5BEB7F99-288B-48A8-98DE-F2FBD22AFE75@.microsoft.com...
> Hi,
> I have 3 Tables with After Update Triggers.
> They all cascade like City -> Province -> State (as a simplified example)
> If I Update a bit (A) in City, a Trigger Sets a bit (B) in Province.
> If I Update the bit B in Province, a Trigger Sets a Bit (C) in State.
> This works.
> However:
> If I Set A, a trigger on table City sets B BUT a trigger on table Province
> DOESN'T in turn Set C in table State.
> So: Individually the Triggers work, but they don't Cascade their action.
> Any advise?
> TIA,
>
> Michael
>|||There is a server option "Allow triggers to be fired which fire other
triggers (nested triggers)". Here is the description copied from BO:
1. Expand a server group.
2. Right-click a server, and then click Properties.
3. Click the Server Settings tab.
4. Under Server behavior, select or clear the Allow triggers to be
fired which fire other triggers (nested triggers) check box.
For more information, see Books Online, article "Using Nested Triggers"|||Hi Sergei,
Thanks for your advise!
I just found an article on this:
http://www.sqlservercentral.com/col.../triggers_1.asp
Nested and Recursive Triggers
Nested triggers are triggers that fire due to actions of other triggers.
For instance, I delete a row from TableA. A trigger on TableA fires to
delete rows from TableB. Because I'm deleting rows from TableB, a trigger
fires on TableB to record the deletes. This is an example of a nested
trigger. As we've talked about, SQL Server 7.0 doesn't support cascading
updates and deletes based on foreign key relationships. Therefore, if we
want to relate our data we can't use DRI and must resort to triggers or some
application oversight. Let's say we've got a cascade delete to fire down 3
or 4 tables. Nested triggers are our answer. Our delete on a particular
table fires a trigger which deletes rows for another table, which fires a
trigger, so on and so forth. SQL Server 7 and 2000 support up to 32 levels
of nested triggers.
Now the big question is, does my SQL Server allow nested triggers? That's
an easy question to answer. It's on by default, but in Query Analyzer we ca
n
issue the following command:
EXEC sp_configure 'nested triggers'
If your run_value is set to 0, your server isn't allowing nested triggers.
If it's set to 1, nested triggers may fire. This is a server wide setting.
Now, to change your setting, once again use the sp_configure command:
To turn off nested triggers:
EXEC sp_configure 'nested triggers', 0
RECONFIGURE
To turn on nested triggers:
EXEC sp_configure 'nested triggers', 1
RECONFIGURE
"Sergei Almazov" wrote:

> There is a server option "Allow triggers to be fired which fire other
> triggers (nested triggers)". Here is the description copied from BO:
> 1. Expand a server group.
> 2. Right-click a server, and then click Properties.
> 3. Click the Server Settings tab.
> 4. Under Server behavior, select or clear the Allow triggers to be
> fired which fire other triggers (nested triggers) check box.
> For more information, see Books Online, article "Using Nested Triggers"
>|||I wonder why the defualt setting is off for this. (Protecting us from
potentially faulty programming perhaps?) ;-) Also, seems like it would be
handy to be able to turn it on per table or database, rather than at the
server level.
In my case where I'm just trying to perform some actions first with the
information in detail records that will be deleted, would it be better to
turn off the cascade delete for the related table and handle deleting of the
detail records in the delete trigger of the master table, or is it better to
allow nested triggers?
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1127473954.892200.319820@.g43g2000cwa.googlegroups.com...
> There is a server option "Allow triggers to be fired which fire other
> triggers (nested triggers)". Here is the description copied from BO:
> 1. Expand a server group.
> 2. Right-click a server, and then click Properties.
> 3. Click the Server Settings tab.
> 4. Under Server behavior, select or clear the Allow triggers to be
> fired which fire other triggers (nested triggers) check box.
> For more information, see Books Online, article "Using Nested Triggers"
>|||I wonder why the defualt setting is off for this. (Protecting us from
potentially faulty programming perhaps?) ;-) Also, seems like it would be
handy to be able to turn it on per table or database, rather than at the
server level.
In my case where I'm just trying to perform some actions first with the
information in detail records that will be deleted, would it be better to
turn off the cascade delete for the related table and handle deleting of the
detail records in the delete trigger of the master table, or is it better to
allow nested triggers?
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1127473954.892200.319820@.g43g2000cwa.googlegroups.com...
> There is a server option "Allow triggers to be fired which fire other
> triggers (nested triggers)". Here is the description copied from BO:
> 1. Expand a server group.
> 2. Right-click a server, and then click Properties.
> 3. Click the Server Settings tab.
> 4. Under Server behavior, select or clear the Allow triggers to be
> fired which fire other triggers (nested triggers) check box.
> For more information, see Books Online, article "Using Nested Triggers"
>|||Hi Bill,
What scares me is that someone or something else can disable your nested
triggers because it's DataServer-Wide :-(((
"Bill Hicks" wrote:

> I wonder why the defualt setting is off for this. (Protecting us from
> potentially faulty programming perhaps?) ;-) Also, seems like it would be
> handy to be able to turn it on per table or database, rather than at the
> server level.
> In my case where I'm just trying to perform some actions first with the
> information in detail records that will be deleted, would it be better to
> turn off the cascade delete for the related table and handle deleting of t
he
> detail records in the delete trigger of the master table, or is it better
to
> allow nested triggers?
> "Sergei Almazov" <almazik@.ukr.net> wrote in message
> news:1127473954.892200.319820@.g43g2000cwa.googlegroups.com...
>
>

Cascading stock values?

Hi,
I have a database containing products, the tables of which are basically as
follows:
table_PRODUCTS
-->(products may or may not have colours)
table_PRODUCT_Colours
-->(colours may or may not have sizes)
table_PRODUCT_Colour_Sizes
- The tables currently hold stock values at all 3 levels, and the sum of
stock at Colour_Sizes level for each product must equal the sum of the stock
at Colours level, which must also equal the stock level held at the main
Product level.
- Some Products may not have Colour_Size records, and some may not have
Colours either.
I am trying to find out what is the best way to keep the 3 levels of stock
consistent, but I think the best way to do it would be:
- Using Triggers, and
- Always only allow stock to be adjusted at the highest level for a
particular product, i.e. Check for higher levels, and if found don't allow
updates to the level in question
- If an UPDATE, DELETE or INSERT operation occurs at the highest level, then
use the trigger to adjust the stock at the lower level automatically.
Please can you tell me whether this is the best way to do it, and if so any
pointers about how I would go about setting the triggers up - Although I hav
e
many years experience of TSQL, I have not used triggers before.
Thanks, Mike.You probably already know that these aggregate values violate database
normalization, and that problems you are talking about come from that
violation..
If these values are not used frequently OR there are few rows... I would NOT
stored the aggregates . Instead provide views over the tables which provide
the aggregate values..
If you MUST denormalize AND the upper level must always be the sum of the
lower levels, then there is a consistency problem I do not understand...If
the parent is always the sum of the children, but there may not be children
rows, then is the parent value 0 ( or null)...? If you allow the values to
be adjusted ONLY at the highest level, how does that allocate to the lower
levels?...
It seems to me you would ONLY allow changes at the lowest level, and the
higher levels would be calculated...
In the end if you mus do this... you might need to post more details ( with
some sample rows)...
By the way, it IS normal to maintain denormalized fields via Triggers... So
you are on the right technology track..
Good luck
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Mike Owen" wrote:

> Hi,
> I have a database containing products, the tables of which are basically a
s
> follows:
> table_PRODUCTS
> -->(products may or may not have colours)
> table_PRODUCT_Colours
> -->(colours may or may not have sizes)
> table_PRODUCT_Colour_Sizes
>
> - The tables currently hold stock values at all 3 levels, and the sum of
> stock at Colour_Sizes level for each product must equal the sum of the sto
ck
> at Colours level, which must also equal the stock level held at the main
> Product level.
> - Some Products may not have Colour_Size records, and some may not have
> Colours either.
> I am trying to find out what is the best way to keep the 3 levels of stock
> consistent, but I think the best way to do it would be:
> - Using Triggers, and
> - Always only allow stock to be adjusted at the highest level for a
> particular product, i.e. Check for higher levels, and if found don't allow
> updates to the level in question
> - If an UPDATE, DELETE or INSERT operation occurs at the highest level, th
en
> use the trigger to adjust the stock at the lower level automatically.
> Please can you tell me whether this is the best way to do it, and if so an
y
> pointers about how I would go about setting the triggers up - Although I h
ave
> many years experience of TSQL, I have not used triggers before.
>
> Thanks, Mike.|||You'd be in a lot less trouble if you had normalized the data model correctl
y.
As I see it:
1) Entities:
Products
2) Attributes:
Colour
Size
3) Relationships:
Products <-- Colour (one to zero or many)
Products <-- Size (one to zero or many)
With proper normalization nothing can stop you.
Consider changing the schema and just maybe the question you were trying to
ask will be answered as if by itself.
ML
http://milambda.blogspot.com/|||Thanks for the quick response Wayne.
Yes, you are right it is not necessarily a good / normalised design.
In answer to your 3rd paragraph " If you MUST denormalize AND ...", it
simply comes down to the fact that all products have the highest
(table_PRODUCT) level record, e.g. A toaster, some products also have the
second level, e.g. A car (blue, red, green etc), and a few have all 3 levels
,
e.g. A pair of trousers (blue, green, red) in various sizes (blue 32" waist,
blue 34" waist etc), so not all parent records have children.
It seems as though from your comment I was thinking along the right lines.
So it seems that as you have partly suggested I would probably need the
following triggers/rules:
- Child stock can always be updated, but when it is always update the parent
stock by adding up all of the childs peers stock
- If a parent has any children, don't let the stock be updated apart from by
a trigger from a child stock change.
Would you think this covers it?, and if so what would the triggers roughly
look like?
Thanks, Mike.
"Wayne Snyder" wrote:
> You probably already know that these aggregate values violate database
> normalization, and that problems you are talking about come from that
> violation..
> If these values are not used frequently OR there are few rows... I would N
OT
> stored the aggregates . Instead provide views over the tables which provid
e
> the aggregate values..
> If you MUST denormalize AND the upper level must always be the sum of the
> lower levels, then there is a consistency problem I do not understand...If
> the parent is always the sum of the children, but there may not be childre
n
> rows, then is the parent value 0 ( or null)...? If you allow the values t
o
> be adjusted ONLY at the highest level, how does that allocate to the lower
> levels?...
> It seems to me you would ONLY allow changes at the lowest level, and the
> higher levels would be calculated...
> In the end if you mus do this... you might need to post more details ( wit
h
> some sample rows)...
> By the way, it IS normal to maintain denormalized fields via Triggers...
So
> you are on the right technology track..
> Good luck
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Mike Owen" wrote:
>|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:DDECA756-D376-4F0D-845B-1C68D229A344@.microsoft.com...
> ...With proper normalization nothing can stop you.
So essentially, "proper normalization" makes you invincible. :-)|||Unfortunately it's a system that I inhereted, but surely you would still hav
e
the same problem even if you did it as you indicated below, e.g.
Keeping it simple you might have a situation where a particular product has
no colours, very simple you would simply update the stock directly against
it, but another product may have colours, in which case you would either hav
e
to:
- ignore the stock at product record level altogether, or
- use triggers at the colour level to keep the stock value at product level
up to date
If you chose the first option you would then have to write application level
code for anything that looks at product level stock in this case, so it is
either not seen, or is swapped for colour level stock.
Cheers, Mike.
"ML" wrote:

> You'd be in a lot less trouble if you had normalized the data model correc
tly.
> As I see it:
> 1) Entities:
> Products
> 2) Attributes:
> Colour
> Size
> 3) Relationships:
> Products <-- Colour (one to zero or many)
> Products <-- Size (one to zero or many)
> With proper normalization nothing can stop you.
> Consider changing the schema and just maybe the question you were trying t
o
> ask will be answered as if by itself.
>
> ML
> --
> http://milambda.blogspot.com/|||Of course all combinations should be considered:
Product : Colour : Size
value null null
value value null
value value value
value null value
This way a specific combination of values represents an instance of a produc
t.
Is this correct? Maybe you should post some representative data, so that we
can understand the issue correctly.
ML
http://milambda.blogspot.com/|||Absolutely. :) Have you never heard of the RDBMS-Man? He's fully normalized
and bullet-proof.
ML
http://milambda.blogspot.com/|||Thanks for all of your help and support.
Following this I had a go at doing my first set of triggers, and they all
seem to work fine how ever many levels I have got.
Cheers, Mike.
"ML" wrote:

> Of course all combinations should be considered:
> Product : Colour : Size
> value null null
> value value null
> value value value
> value null value
> This way a specific combination of values represents an instance of a prod
uct.
> Is this correct? Maybe you should post some representative data, so that w
e
> can understand the issue correctly.
>
> ML
> --
> http://milambda.blogspot.com/

Cascading path problem

Hello,
The following SQL code produces the famous multiple cascading paths problem.
How should I design the tables to have the below functionality, but keep the
cascading paths? A Doc doesn't necessarily have to be related to a Folder,
but must be related to a Cust.
Changing ON UPDATE to NO ACTION would solve it partly, but it just doesn't
feel right.
Thanks for any help!
cheers,
Jonah
CREATE TABLE Cust (
usr_name varchar(20) NOT NULL,
usr_pwd varchar(40) NOT NULL,
customer_name nvarchar(50) NOT NULL,
created_date datetime default getdate() NOT NULL,
change_date datetime default getdate() NOT NULL,
deactivate_date datetime default getdate() NULL
) ON [PRIMARY]
GO
ALTER TABLE Cust ADD CONSTRAINT
PK_Cust PRIMARY KEY CLUSTERED
(
usr_name
) ON [PRIMARY]
GO
CREATE TABLE Folders (
folder_id int NOT NULL ,
folder_name nvarchar(20) NOT NULL ,
folder_description nvarchar(150) NULL ,
usr_name varchar(20) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE Folders ADD CONSTRAINT
PK_Folders PRIMARY KEY CLUSTERED
(
folder_id
) ON [PRIMARY]
GO
ALTER TABLE Folders ADD CONSTRAINT
FK_Folders_Cust FOREIGN KEY
(
usr_name
) REFERENCES Cust
(
usr_name
) ON UPDATE CASCADE
GO
CREATE TABLE Docs (
doc_id int NOT NULL,
header nvarchar(255) not null,
created_date datetime default getdate() NOT NULL,
updated_date datetime default getdate() NOT NULL,
usr_name varchar(20) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE Docs ADD CONSTRAINT
PK_Docs PRIMARY KEY CLUSTERED
(
doc_id
) ON [PRIMARY]
GO
ALTER TABLE Docs ADD CONSTRAINT
FK_Cust_Docs FOREIGN KEY
(
usr_name
) REFERENCES Cust
(
usr_name
) ON UPDATE CASCADE
ON DELETE NO ACTION
GO
CREATE TABLE DocsInFolders
(
doc_id int NOT NULL,
folder_id int NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE DocsInFolders ADD CONSTRAINT
PK_DocsInFolders PRIMARY KEY CLUSTERED
(
doc_id,
folder_id
) ON [PRIMARY]
GO
ALTER TABLE DocsInFolders ADD CONSTRAINT
FK_DocsInFolders_Folders FOREIGN KEY
(
folder_id
) REFERENCES Folders
(
folder_id
) ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE DocsInFolders ADD CONSTRAINT
FK_DocsInFolders_Docs FOREIGN KEY
(
doc_id
) REFERENCES Docs
(
doc_id
) ON UPDATE CASCADE
ON DELETE CASCADE
GOYou say 'A Doc *doesn't necessarily have to be* related to a Folder,
but *must be* related to a Cust.'
Keep cascading on FK_DocsInFolders_Docs, but manage the Cust<--Docs
relationship in procedures. You make it sound as if the importance of the
Cust<--Docs relationship supercedes the importance of the Folders<--Docs
relationship.
Vital relationships should not be cascading.
ML|||Correct. The Cust<--Docs relationship is more importance, but that's why I
have only ON UPDATE CASCADE (to make possible usr_name changes up to date)
and not ON DELETE because I don't want Custs having Docs deleted by mistake.
An SP takes care of that.
So what you are saying is that I should change the ON CHANGE to NO ACTION as
well?
/Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:601F4976-E669-4865-9D92-FBAF9A16CA8F@.microsoft.com...
> You say 'A Doc *doesn't necessarily have to be* related to a Folder,
> but *must be* related to a Cust.'
> Keep cascading on FK_DocsInFolders_Docs, but manage the Cust<--Docs
> relationship in procedures. You make it sound as if the importance of the
> Cust<--Docs relationship supercedes the importance of the Folders<--Docs
> relationship.
> Vital relationships should not be cascading.
>
> ML|||If usr_name can be changed then using it as a primary key (and/or referencin
g
it from a foreign key table) is really bad practice. Either disallow usr_nam
e
changes or use a better kandidate key.
I wouldn't allow cascades for this one.
ML|||OK. So if I disallow cascades for usr_name, you would consider the design to
be correct?
/Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:90F300A3-7104-4924-A594-E28AC05D645D@.microsoft.com...
> If usr_name can be changed then using it as a primary key (and/or
> referencing
> it from a foreign key table) is really bad practice. Either disallow
> usr_name
> changes or use a better kandidate key.
> I wouldn't allow cascades for this one.
>
> ML|||As far as I can see, the design is fine. I would, however, do something abou
t
the Folders and Docs entities. Right now you allow a single document to exis
t
in more than one folder, which can lead to problems. The same goes for
folders - you should focus on preventing circular references.
Oh, and if any given document cannot exist in more than one folder, then the
DocsInFolders table is obsolete. You could simply add a nullable folder_id
foreign key to the Docs table (nullable since you've mentioned that a
document need not exist in any folder).
I hope you started on paper. :) And in case you haven't, maybe you'll do it
next time.
ML|||My design question applied to the cascading paths, not the business rules
themselves. One Doc may actually exist in several Folders.
- One Cust may have zero or more Folders
- One Cust may have zero or more Docs not connected to a Folder
- One Folder may have zero or more Docs related
Thus, my question was only related to if there's a better way of designing
the relations and tables to avoid circular references, which now occurs.
FYI, I didn't start on paper. I use Visio.
Thank you,
Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:F981A87A-893F-4940-9EBD-566C385CF1CB@.microsoft.com...
> As far as I can see, the design is fine. I would, however, do something
> about
> the Folders and Docs entities. Right now you allow a single document to
> exist
> in more than one folder, which can lead to problems. The same goes for
> folders - you should focus on preventing circular references.
> Oh, and if any given document cannot exist in more than one folder, then
> the
> DocsInFolders table is obsolete. You could simply add a nullable folder_id
> foreign key to the Docs table (nullable since you've mentioned that a
> document need not exist in any folder).
> I hope you started on paper. :) And in case you haven't, maybe you'll do
> it
> next time.
>
> ML|||No pun intended.
You are right - there is a better way to avoid circular references. You
might find more answers studying trees and hierarchies. Consider this model:
ItemInstance : ItemID : BelongsToInstance : ItemType
ItemInstance is unique.
ItemID can be either cust_id, folder_id or doc_id.
BelongsToInstance is a foreign key referencing ItemInstance.
ItemType designates whether ItemID is customer, folder or document.
Valid relationships are:
1) Customer/Folder/Document
2) Customer/Document
3) Folder/Folder (<-- not sure about this one, but seems logical, however:
parent folder_id must should be equal to child folder_id).
A customer can only exist as a root element (BelongsToInstance is null).
A Document can only exist as a leaf element (its ItemInstance is never
referenced in a BelongsToInstance).
The above constraints could be reinforced through the use of indexed views.
ItemInstance and BelongsToInstance prevent circular references while still
allowing all possible relationships between the three entities.
ML|||Thank you for your detailed answer.
I do have a copy of a trees and hierarchies book which I could take a closer
look into (Joe Celko's Trees and Hierarchies in SQL for Smarties). Maybe I
can find some more answers and examples there.
/Jonah
"ML" <ML@.discussions.microsoft.com> skrev i meddelandet
news:D5B9F5D8-4790-4088-BF18-14DE5119891D@.microsoft.com...
> No pun intended.
> You are right - there is a better way to avoid circular references. You
> might find more answers studying trees and hierarchies. Consider this
> model:
> ItemInstance : ItemID : BelongsToInstance : ItemType
> ItemInstance is unique.
> ItemID can be either cust_id, folder_id or doc_id.
> BelongsToInstance is a foreign key referencing ItemInstance.
> ItemType designates whether ItemID is customer, folder or document.
> Valid relationships are:
> 1) Customer/Folder/Document
> 2) Customer/Document
> 3) Folder/Folder (<-- not sure about this one, but seems logical, however:
> parent folder_id must should be equal to child folder_id).
> A customer can only exist as a root element (BelongsToInstance is null).
> A Document can only exist as a leaf element (its ItemInstance is never
> referenced in a BelongsToInstance).
> The above constraints could be reinforced through the use of indexed
> views.
> ItemInstance and BelongsToInstance prevent circular references while still
> allowing all possible relationships between the three entities.
>
> ML|||What about using a nested sets model for the hierarchy? I am not a big
fanof misxed node trees, but this case is pretty easy:
1) If it is the root node, it is a Customer
2) If it is a leaf node, it is a document
3) other it is a folder
CREATE TABLE DocumentHierarchy
(customer_id INTEGER NOT NULL
REFERENCES Customers
ON UPDATE CASCADE
ON DELETE CASCADE,
folder_id INTEGER -- null means no folder
REFERENCES Folders (folder_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
document_id INTEGER -- null means no document
REFERENCES Documents(doc_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
lft INTEGER NOT NULL CHECK (lft >0),
rgt INTEGER NOT NULL CHECK (rgt >lft),
PRIMARY KEY (customer_id, lft, rgt));
untested. You can also get a copy of TREES & HIERARCHIES IN SQL for
more ideas.

Cascading Parameters, Drop down lists & the use of "ALL"

I have a report I'm working on (data dictionary) where the team wants to be
able to do the following:
1) Choose "All Tables" and get a report listing all tables with each tables
column names & properties underneath the proper table (ie, the columnName
param would be greyed out if this is chosen).
OR
2) Choose a single table by name, then choose "All Columns" and get a report
with ONLY that table and only the columns belonging to that table underneath
it with their properties.
OR
3) Choose a single table by name, then choose a single column by name and
only get that single column's properites.
Obviously, 3 is the easiest and what I was able to get done first. I have a
seperate report (my first report) where I've got a list of all tables with
drilldown enabled that shows each table's columns (all of them) when
expanded. Of course, once I got that report done, I was asked to add this
additional functionality.
Can this be done in one report or will I need to resort to multiple,
seperate reports to accomplish this?
I've got the code written for "All Tables" and "All Columns", but when I try
to pick a single table, I still get a ColumnList that lists all columns in
the entire DB, not the columns specific for that table.
Can someone give me advice on how to accomplish what I need? Thanks!!!
CatadminWe do something similar (although no cascading parameters). For "All", we
pass in the value zero, and the query is written like this:
Select * From OurTable Where (@.ID = 0 Or ID = @.ID)
If you're doing actual database objects and are using the sysobjects and
syscolumns tables, I would imagine your syscolumns query would include a
similar clause:
Where (@.TableID = 0 or id = @.TableID) And (@.ColumnID = 0 or colid =@.ColumnID)
I don't use cascading parameters, so I may be completely missing the point,
and if so, I apologize...
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:D0E39CA8-BB08-47BA-8D98-4DCF30A23C43@.microsoft.com...
>I have a report I'm working on (data dictionary) where the team wants to be
> able to do the following:
> 1) Choose "All Tables" and get a report listing all tables with each
> tables
> column names & properties underneath the proper table (ie, the columnName
> param would be greyed out if this is chosen).
> OR
> 2) Choose a single table by name, then choose "All Columns" and get a
> report
> with ONLY that table and only the columns belonging to that table
> underneath
> it with their properties.
> OR
> 3) Choose a single table by name, then choose a single column by name and
> only get that single column's properites.
> Obviously, 3 is the easiest and what I was able to get done first. I have
> a
> seperate report (my first report) where I've got a list of all tables with
> drilldown enabled that shows each table's columns (all of them) when
> expanded. Of course, once I got that report done, I was asked to add this
> additional functionality.
> Can this be done in one report or will I need to resort to multiple,
> seperate reports to accomplish this?
> I've got the code written for "All Tables" and "All Columns", but when I
> try
> to pick a single table, I still get a ColumnList that lists all columns in
> the entire DB, not the columns specific for that table.
> Can someone give me advice on how to accomplish what I need? Thanks!!!
> Catadmin
>|||I appreciate the suggestion, but I'm not sure it will work.
I want to be able to grey out the column parameter if "All Tables" is
chosen. Make it not accessible. Then, if a single table is chosen, not
print the other table on the report. My first problem is, that even when I
choose a single table, it prints out ALL the tables with the single column I
chose & all associated properties underneath all tables, even the tables that
have no such column. My second problem is creating the second parameter's
drop down list based only the columns associated with a single table picked
in the first parameter. My third problem is forcing all columns to
automatically be chosen, blocking out the second parameter, if "All Tables"
is chosen, so that all tables print, in order, with all of the columns
associated with that table and only that table.
Thank you for your time, though.
Catadmin
"DJM" wrote:
> We do something similar (although no cascading parameters). For "All", we
> pass in the value zero, and the query is written like this:
> Select * From OurTable Where (@.ID = 0 Or ID = @.ID)
> If you're doing actual database objects and are using the sysobjects and
> syscolumns tables, I would imagine your syscolumns query would include a
> similar clause:
> Where (@.TableID = 0 or id = @.TableID) And (@.ColumnID = 0 or colid => @.ColumnID)
> I don't use cascading parameters, so I may be completely missing the point,
> and if so, I apologize...
> "Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
> news:D0E39CA8-BB08-47BA-8D98-4DCF30A23C43@.microsoft.com...
> >I have a report I'm working on (data dictionary) where the team wants to be
> > able to do the following:
> >
> > 1) Choose "All Tables" and get a report listing all tables with each
> > tables
> > column names & properties underneath the proper table (ie, the columnName
> > param would be greyed out if this is chosen).
> >
> > OR
> >
> > 2) Choose a single table by name, then choose "All Columns" and get a
> > report
> > with ONLY that table and only the columns belonging to that table
> > underneath
> > it with their properties.
> >
> > OR
> >
> > 3) Choose a single table by name, then choose a single column by name and
> > only get that single column's properites.
> >
> > Obviously, 3 is the easiest and what I was able to get done first. I have
> > a
> > seperate report (my first report) where I've got a list of all tables with
> > drilldown enabled that shows each table's columns (all of them) when
> > expanded. Of course, once I got that report done, I was asked to add this
> > additional functionality.
> >
> > Can this be done in one report or will I need to resort to multiple,
> > seperate reports to accomplish this?
> >
> > I've got the code written for "All Tables" and "All Columns", but when I
> > try
> > to pick a single table, I still get a ColumnList that lists all columns in
> > the entire DB, not the columns specific for that table.
> >
> > Can someone give me advice on how to accomplish what I need? Thanks!!!
> >
> > Catadmin
> >
>
>

Cascading deletions...

Hello,
A quick question about cascading deletions...
Say I have two tables with the following data
Table A
index data
1 "some data"
2 "some other data"
Table B
index A-index data
1 2 "something"
2 1 "something else"
3 1 "out of this world"
These tables set up to perform cascading deletions when
records are removed.
My question is this: If I remove index 3 from table B,
will index 1 from table A be removed?
If not, how do you get cascading deletion to work?
Thanks
MeNo, cascade delete works "top-to-bottom", not "bottom-to-top" meaning a
foreign key with "on delete cascade", will only fire when A-index "1" is
removed from Table A. What you are seeking would cause a violation of the
foreign key because Index 2 in Table B still has reference to A-Index "2".
If you need to remove reference to all records in Table B with A-Index "1",
you need to write a trigger for the Delete event.
HTH,
Morgan
"Me" <anonymous@.discussions.microsoft.com> wrote in message
news:03f001c3c9a7$952dbd30$a101280a@.phx.gbl...
> Hello,
> A quick question about cascading deletions...
> Say I have two tables with the following data
> Table A
> index data
> 1 "some data"
> 2 "some other data"
> Table B
> index A-index data
> 1 2 "something"
> 2 1 "something else"
> 3 1 "out of this world"
>
> These tables set up to perform cascading deletions when
> records are removed.
> My question is this: If I remove index 3 from table B,
> will index 1 from table A be removed?
> If not, how do you get cascading deletion to work?
> Thanks
> Me|||Sorry this should have read..
If you need to remove the record(s) in Table A with A-Index "1" when a
record from Table B is removed, you will need to write a trigger for the
Delete event on Table B.
"Morgan" <zakirmahmood@.primposta.com> wrote in message
news:euEQE2ayDHA.2064@.TK2MSFTNGP10.phx.gbl...
> No, cascade delete works "top-to-bottom", not "bottom-to-top" meaning a
> foreign key with "on delete cascade", will only fire when A-index "1" is
> removed from Table A. What you are seeking would cause a violation of the
> foreign key because Index 2 in Table B still has reference to A-Index "2".
> If you need to remove reference to all records in Table B with A-Index
"1",
> you need to write a trigger for the Delete event.
> HTH,
> Morgan
> "Me" <anonymous@.discussions.microsoft.com> wrote in message
> news:03f001c3c9a7$952dbd30$a101280a@.phx.gbl...
> > Hello,
> >
> > A quick question about cascading deletions...
> >
> > Say I have two tables with the following data
> >
> > Table A
> > index data
> > 1 "some data"
> > 2 "some other data"
> >
> > Table B
> > index A-index data
> > 1 2 "something"
> > 2 1 "something else"
> > 3 1 "out of this world"
> >
> >
> > These tables set up to perform cascading deletions when
> > records are removed.
> >
> > My question is this: If I remove index 3 from table B,
> > will index 1 from table A be removed?
> >
> > If not, how do you get cascading deletion to work?
> >
> > Thanks
> > Me
>|||Thanks for the info !!
>--Original Message--
>Sorry this should have read..
> If you need to remove the record(s) in Table A with A-
Index "1" when a
>record from Table B is removed, you will need to write a
trigger for the
>Delete event on Table B.
>"Morgan" <zakirmahmood@.primposta.com> wrote in message
>news:euEQE2ayDHA.2064@.TK2MSFTNGP10.phx.gbl...
>> No, cascade delete works "top-to-bottom", not "bottom-
to-top" meaning a
>> foreign key with "on delete cascade", will only fire
when A-index "1" is
>> removed from Table A. What you are seeking would cause
a violation of the
>> foreign key because Index 2 in Table B still has
reference to A-Index "2".
>> If you need to remove reference to all records in Table
B with A-Index
>"1",
>> you need to write a trigger for the Delete event.
>> HTH,
>> Morgan
>> "Me" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:03f001c3c9a7$952dbd30$a101280a@.phx.gbl...
>> > Hello,
>> >
>> > A quick question about cascading deletions...
>> >
>> > Say I have two tables with the following data
>> >
>> > Table A
>> > index data
>> > 1 "some data"
>> > 2 "some other data"
>> >
>> > Table B
>> > index A-index data
>> > 1 2 "something"
>> > 2 1 "something else"
>> > 3 1 "out of this world"
>> >
>> >
>> > These tables set up to perform cascading deletions
when
>> > records are removed.
>> >
>> > My question is this: If I remove index 3 from table
B,
>> > will index 1 from table A be removed?
>> >
>> > If not, how do you get cascading deletion to work?
>> >
>> > Thanks
>> > Me
>>
>
>.
>

cascading deletes through a 1-M join table

Hi all,
I have three tables, two with unique integer PK's and a join table for a 1-M
join.
PARENT JOIN CHILD
+--+ +--+--+ +--+
+ 7 + --> + 7 + 45 + --> + 45 +
+--+ +--+--+ +--+
+ 7 + 197 + --> + 197 +
+--+--+ +--+
I want to be able to delete a PARENT record and have it clean up the JOIN
and all CHILD entries.
The join table is the Foreign key table. If I set up cascade delete from
Parent to JOIN that works, but i can't set up cascade delete from JOIN to
CHILD because the keys in the JOIN table are not primary keys.
I know that I can do this in a trigger on JOIN but this is a natural
straight parent->child delete, is there a way to cascade delete through the
JOIN to the CHILD?
thanks!
John"John Mott" <johnmott59@.hotmail.com> wrote in message
news:%23A0gjCoUGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I have three tables, two with unique integer PK's and a join table for a
> 1-M join.
> PARENT JOIN CHILD
> +--+ +--+--+ +--+
> + 7 + --> + 7 + 45 + --> + 45 +
> +--+ +--+--+ +--+
> + 7 + 197 + --> + 197 +
> +--+--+ +--+
> I want to be able to delete a PARENT record and have it clean up the JOIN
> and all CHILD entries.
> The join table is the Foreign key table. If I set up cascade delete from
> Parent to JOIN that works, but i can't set up cascade delete from JOIN to
> CHILD because the keys in the JOIN table are not primary keys.
> I know that I can do this in a trigger on JOIN but this is a natural
> straight parent->child delete, is there a way to cascade delete through
> the JOIN to the CHILD?
>
Create a unique index on the JOIN table to support the relationship to
CHILD.
David

cascading delete?

I use SQL Server 2005
I have tables tblUserData, tblUsersAndGuestbook, tblGuestbook

tblUserdata contains:
UserCode int
Username nvarchar(50)

tblUsersAndGuestbook contains:
Usercode int (FK to tblUserData)
GBEntryCode int

tblGuestbook
GBEntryCode int (FK to tblUsersAndGuestbook)
GBText text

Now...if I delete a user in tblUserData I want to also delete the entries in tblUsersAndGuestbook AND in tblGuestbook.
I've heard something about cascading delete, but how can i configure that in my database?
Or do I manually need to delete all entries from code?

Im a bit lazy and never did this in code in the last 5 years, but when you define your FK in either Visual Studio or in SQL Management Studio, in the relations properties of the tables, pick the FK, go in its properties, under Database Designer you have the option Insert and Update Specification. There, Update Rule and Delete rule can be set to cascade.

In a master -> detail (1 to N) scenario, if you delete a row from the master, all the detail is automatically deleted along with it.

Its pretty dangerous though, I prefer to avoid it and do it manually in a transaction.

|||

Peter Smith:

I use SQL Server 2005
I have tables tblUserData, tblUsersAndGuestbook, tblGuestbook

tblUserdata contains:
UserCode int
Username nvarchar(50)

tblUsersAndGuestbook contains:
Usercode int (FK to tblUserData)
GBEntryCode int

tblGuestbook
GBEntryCode int (FK to tblUsersAndGuestbook)
GBText text

Now...if I delete a user in tblUserData I want to also delete the entries in tblUsersAndGuestbook AND in tblGuestbook.
I've heard something about cascading delete, but how can i configure that in my database?
Or do I manually need to delete all entries from code?

Try these two threads of how to enable it, DRI(declarative referential integrity) is very simple if a references b then b must exist. And it has replaced most triggers for the last five years. Hope this helps.

http://forums.asp.net/thread/1315554.aspx

http://forums.asp.net/thread/1120122.aspx

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!
On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:

>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:

>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascading Delete Problem

I have three tables that equate to a customer - order - order items
scenario.
The Customer table has 100,000 rows
The Order table has 3 million rows
The Order items has 30 million rows.
I have the primary and foreign keys all defind and the appropriate indexes.
I have cascading deletes on the foreign keys between all three tables.
It takes over a minute to delete a customer. If I remove the cacading
delete, and delete from the order items then orders then customers it takes
seconds.
Why does the cascading deletes take so long?
Using SQL2000 sp3a
Thanks for any insight!On Thu, 03 Feb 2005 19:35:25 GMT, Howard Carr wrote:
>I have three tables that equate to a customer - order - order items
>scenario.
>The Customer table has 100,000 rows
>The Order table has 3 million rows
>The Order items has 30 million rows.
>I have the primary and foreign keys all defind and the appropriate indexes.
>I have cascading deletes on the foreign keys between all three tables.
>It takes over a minute to delete a customer. If I remove the cacading
>delete, and delete from the order items then orders then customers it takes
>seconds.
>Why does the cascading deletes take so long?
Hi Howard,
Is your primary key (or unique) constraint for the OrderItems table
defined as (OrderID, CustomerID)?
Try changing it to (CustomerID, OrderID). It will speed up the deletion of
a customer, but it will slow down the deletion of an order. If both
deletion happens frequently enough to need good performance, you might try
adding an extra index, so that both orders are present.
Note that changing indexes might affect performance of all other queries
as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascading delete

I have one master tables with 4 relational tables
The relationship between the tables is not cascading delete.
But I like to write one query that will delete record from the master table
as well as relational tables.
Let me know how can i do this.
Thanks in advanceThis newsgroup is for reporting services. Try one of the other SQL Server
newsgroups.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"vinesh" <vineshk@.visitomega.com> wrote in message
news:%23%2355VDOtEHA.220@.TK2MSFTNGP15.phx.gbl...
> I have one master tables with 4 relational tables
> The relationship between the tables is not cascading delete.
> But I like to write one query that will delete record from the master
table
> as well as relational tables.
> Let me know how can i do this.
> Thanks in advance
>