[MS SQL Server] Verwijder logica probleem

[sig69]

Ik zit met een klein probleempje, ik zal het proberen in woorden uit te leggen want theforumisdown is gesloten en ik kan nu even niet bij mijn eigen webspace.
Ik heb drie tabellen:

Document
id prim key
file
name

Contract
id prim key
startDate
customer

xContractDocument
contractID prim key
documentID prim key

Ik wil nu als er een contract verwijderd wordt, de bijbehorende documenten ook verwijderen. Aan de hand van de contractID kan ik de documentID's ophalen uit xContractDocument, maar de records uit xContractDocument moeten eerst verwijderd worden omdat ik anders tegen een foreign key constraint aanloop bij het verwijderen van de documenten. Ik moet die contractID's dus ergens opslaan, de records uit xContractDocument verwijderen en dan de documenten verwijderen. Maar waar? Ik zit nu aan een temptable te denken maar dat vind ik niet echt een mooie oplossing eigenlijk. Zijn hier nog andere oplossingen voor?

[whoami]

De foreign key zo definieren dat deletes ge-cascaded worden.

... ON DELETE CASCADE

En als je dat niet kan doen, dan kan je dat in een trigger doen. (Je zal een instead of trigger nodig hebben denk ik), waarmee je dan eerst de records uit de documenten-tabel verwijderd (en hiervoor heb je geen temp table nodig, aangezien je in een trigger de beschikking hebt over de temp tables inserted en deleted), en dan het record uit contract verwijderd.

DELETE FROM tblDocuments, deleted
WHERE deleted.contract_id = tblDocuments.contract_id

DELETE FROM tblContract, deleted WHERE contractid = deleted.contract_id

[ Voor 27% gewijzigd door whoami op 13-05-2004 13:35 ]

[Noork]

Je hoeft toch niks op te slaan.

Je doet eerst een select op documentcontract waar Contract.id = xContractDocument.contractid

Dan heb je alle id's van de documenten en contracten en kun je in een loopje alles verwijderen. Hierna verwijder je pas de xContractDocument

[ Voor 22% gewijzigd door Noork op 13-05-2004 13:59 ]

[cameodski]

Als je per contract documenten hebt, klopt je datamodel niet, want dan moet je in je Document tabel een contractid hebben en dan zal via cascading delete alles netjes verwijderd worden.
En als je je datamodel niet kunt/wilt aanpassen, kun je simpelweg via joins eerst de records uit je document tabel weggooien, dus niks triggers, temp tables, loops of weet ik wat dingen nog meer.

[ Voor 35% gewijzigd door cameodski op 13-05-2004 13:36 ]

[koli-man]

Als ik het zo lees, is het zo:
Een contract kan één of meerdere documenten bevatten, dat zeg.
Waarom zet je in Document dan geen id naar contract?
Dan weet je welk document bij welk contract hoort.
Dan kun je alles verwijderen en kun je diezelfde gegevens krijgen die je nu ook wil.

[sig69]

@cameodski en koli-man:
De tabel documenten wordt ook voor andere documenten gebruikt, daarom is besloten om de koppeling met Contracten op deze manier te maken.

@whoami:
Die triggers klinken interessant, daar ga ik wat meer over lezen.

[cameodski]

sig69 schreef op 13 mei 2004 @ 13:56:
@cameodski en koli-man:
De tabel documenten wordt ook voor andere documenten gebruikt, daarom is besloten om de koppeling met Contracten op deze manier te maken.

Dan kun je het met de volgende queries doen:

code:

DELETE Document
WHERE id IN (
     SELECT documentid
     FROM xContractDocument
     WHERE (x.contractID = variabele)
)

DELETE Contract
WHERE id = variabele

[whoami]

cameodski schreef op 13 mei 2004 @ 14:03:
[...]

Dan kun je het met de volgende queries doen:

code:

DELETE Document
WHERE id IN (
     SELECT documentid
     FROM xContractDocument
     WHERE (x.contractID = variabele)
)

DELETE Contract
WHERE id = variabele

Waarom zou je dat met een subquery doen, en niet met een join?

In de trigger kan je dan ook handig gebruik maken van de deleted en inserted tables.

[ Voor 11% gewijzigd door whoami op 13-05-2004 14:09 ]

[curry684]

whoami schreef op 13 mei 2004 @ 13:33:
De foreign key zo definieren dat deletes ge-cascaded worden.

... ON DELETE CASCADE

En als je dat niet kan doen,

En waarom zou ie dat niet kunnen doen, als ie volgens z'n topictitel gewoon SQL Server gebruikt dat cascading deletes volledig ondersteunt? Tis ook de enige correcte oplossing, triggers is maar een (inefficientere) workaround.

[whoami]

Cascading deletes worden afaik slechts vanaf SQL Server 2000 (of was het nu toch vanaf 7) ondersteund.

[ Voor 17% gewijzigd door whoami op 13-05-2004 14:10 ]

[cameodski]

whoami schreef op 13 mei 2004 @ 14:07:
Waarom zou je dat met een subquery doen, en niet met een join?

Ik denk dat het in dit geval toch de meest efficiënte oplossing is, maar het zou heel goed kunnen dat SQL Server een iets minder handige ook wel weet te optimaliseren.

In de trigger kan je dan ook handig gebruik maken van de deleted en inserted tables.

deleted en inserted tables zijn wel handig alleen worden dan volgens mij geen indexen gebruikt.
En volgens mij kun je beter geen instead of trigger gebruiken. Je kunt dit namelijk gewoon in de normale delete trigger van xContractDocument afhandelen.

whoami schreef op 13 mei 2004 @ 14:09:
Cascading deletes worden afaik slechts vanaf SQL Server 2000 (of was het nu toch vanaf 7) ondersteund.

Inderdaad pas vanaf MSSQL 2000.

[sig69]

Ik gebruik 2000, dus ik heb net even geprobeerd en Cascading deletes werkt prima inderdaad. Toch ga ik nog wat meer lezen over triggers, interessante materie wel.

[whoami]

cameodski schreef op 13 mei 2004 @ 14:17:
[...]

deleted en inserted tables zijn wel handig alleen worden dan volgens mij geen indexen gebruikt.

Hmm, dat weet ik niet. Er zullen idd geen indexen liggen op de deleted / inserted tables, maar wel op de andere table.

En volgens mij kun je beter geen instead of trigger gebruiken. Je kunt dit namelijk gewoon in de normale delete trigger van xContractDocument afhandelen.

Volgens mij lukt het niet als je het niet met een instead of doet, en de trigger mag al helemaal niet op xContractdocument liggen. De trigger moet op de tabel 'Contract' liggen, want als je iets deleted uit Contract, en je trigger ligt op xContractdocument, dan wordt die trigger niet getriggered.
Een gewone trigger (AFTER) zal imo ook niet werken, aangezien dat dan toch eerst het record uit Contract zal verwijderd worden, en dan pas de trigger zal uitgevoerd worden die de records uit de gelinkte tabel (ContractDocument) verwijderd. Door die volgorde zal de FK constraint ook reclameren.
Vandaar : een INSTEAD OF trigger.

Maar curry heeft natuurlijk wel gelijk als hij zegt dat CASCADING deletes te preferen zijn boven die oplossing met triggers (maar de TS moet dan wel gebruik maken van SQL Server 2000).

[cameodski]

whoami schreef op 13 mei 2004 @ 14:26:
Volgens mij lukt het niet als je het niet met een instead of doet, en de trigger mag al helemaal niet op xContractdocument liggen. De trigger moet op de tabel 'Contract' liggen, want als je iets deleted uit Contract, en je trigger ligt op xContractdocument, dan wordt die trigger niet getriggered.
Een gewone trigger (AFTER) zal imo ook niet werken, aangezien dat dan toch eerst het record uit Contract zal verwijderd worden, en dan pas de trigger zal uitgevoerd worden die de records uit de gelinkte tabel (ContractDocument) verwijderd. Door die volgorde zal de FK constraint ook reclameren.
Vandaar : een INSTEAD OF trigger.

Maar curry heeft natuurlijk wel gelijk als hij zegt dat CASCADING deletes te preferen zijn boven die oplossing met triggers (maar de TS moet dan wel gebruik maken van SQL Server 2000).

Lol: instead of triggers zitten er ook pas vanaf MSSQL 2000 in, dus dan kun je beter cascading delete gebruiken.
Maar je hebt inderdaad gelijk dat de trigger beter op de tabel contract kan liggen. Overigens heb je trigger sowieso nodig, omdat je met cascading deletes de records uit de document tabel niet kunt verwijderen. En daar heb je dan weer geen instead of trigger voor nodig.

[sig69]

Nog even iets anders snel tussendoor: hoe zet ik quotes in een string in sql server?
Dus stel ik wil dit hebben:
Naam: 'Piet'
Op de een of andere manier moet ik die ' in de string zelf krijgen, maar ik kan er niets over vinden (alleen over ESCAPE met een LIKE query)

[whoami]

2 quotes gebruiken, parametrized queries gebruiken indien dat van toepassing is, en kan je die escape clause ook niet gebruiken ?

[sig69]

Ik stuur een string naar een SP, met een aantal ID's die verwijderd moeten worden gescheiden door een |
Bijvoorbeeld 1|3|4
Deze wilde ik replacen met een , om dan

code:

DELETE FROM
Tabel
WHERE
id IN (@strIDs)

Maar dit vreet hij om de een of andere reden niet, ik denk dat hij de string dan als '1,3,4' ziet.
Als ik de string replace met REPLACE(@strIDs, '|', ' ',' ') of iets dergelijks zou er '1','3','4' uikomen, wat weer wel werkt. Alleen dat replace gebeuren werkt dus nog niet helemaal

[cameodski]

sig69 schreef op 13 mei 2004 @ 16:55:
Ik stuur een string naar een SP, met een aantal ID's die verwijderd moeten worden gescheiden door een |
Bijvoorbeeld 1|3|4
Deze wilde ik replacen met een , om dan

code:

DELETE FROM
Tabel
WHERE
id IN (@strIDs)

Maar dit vreet hij om de een of andere reden niet, ik denk dat hij de string dan als '1,3,4' ziet.
Als ik de string replace met REPLACE(@strIDs, '|', ' ',' ') of iets dergelijks zou er '1','3','4' uikomen, wat weer wel werkt. Alleen dat replace gebeuren werkt dus nog niet helemaal

Oei, dat is niet zo'n fraaie constructie. Je hebt dan twee mogelijkheden:
1. dynamic sql gebruiken
2. met een loop door je list heen gaan en de records één voor één in een temptable zetten; vervolgens kun je de temptable gebruiken om de records te verwijderen.

[sig69]

Mogelijkheid 3:
de | vervangen door ',' waardoor de uiteindelijke string er dus zo uit ziet: '1','3','4'
Ik weet alleen niet hoe ik die ' er in moet krijgen..

[cameodski]

sig69 schreef op 13 mei 2004 @ 17:11:
Mogelijkheid 3:
de | vervangen door ',' waardoor de uiteindelijke string er dus zo uit ziet: '1','3','4'
Ik weet alleen niet hoe ik die ' er in moet krijgen..

1. Zijn je ID's geen numerieke waarden? Zo nee, waarom niet?
2. Deze oplossing is leuk bedacht, maar werkt alleen als je een dynamische query gebruikt. MSSQL verwacht bij de IN operator een set ipv een string.

[ Voor 9% gewijzigd door cameodski op 13-05-2004 17:17 ]

[sig69]

Je had gelijk ik zat enorm krom te denken (de dag duurt alweer veel te lang). Ik heb het nu opgelost met een dynamische query.