[MS-SQL]count = 1 maar toch 0 rows affected

[marshal]

Ik heb dus een MS-SQL database met daarin een trigger die controleert of de gekozen gebruikersnaam al bestaat. deze ziet er als volgt uit:

SQL:

ALTER  TRIGGER [Check_Username] ON dbo.Gebruiker 
FOR INSERT
AS

declare @ins_username as varchar(10)
declare @count as int

select @count = '0'
select @ins_username = inserted.Username from inserted

/* Ingevoegd voor debug */
print 'count na clear' print @count

Select @count = count(GebruikerID) 
from dbo.Gebruiker 
where Username=@ins_username
If @count > 0
  begin
    rollback
    print 'Error: [Trigger]Username already exists!'

/*Ingevoegd voor debug */
select * from dbo.Gebruiker where Username=@ins_username

    return
  end

Ik voer dus een een sql-query uit die een aantal gegevens in de database zet. Een van die gegevens is Username (waarvan ik zeker weet dat die niet bestaat).
Nu komt het vreemde, de waarde count wordt dus 1, maar het select statement geeft 0 rijen terug.

count na clear
0
Error: [Trigger]Username already exists!

(0 row(s) affected)

Ik dacht dat de variabele @count wellicht niet geleegd werd, dat heb ik dus expliciet nogmaals gedaan en daarna is die waarde dus ook 0.
De trigger lijkt mij geen fouten te bevatten, waarom het dus mis gaat is mij een compleet raadsel.

Iemand die hier een oplossing voor heeft?

offtopic:
Ik heb dus een projectleider die mij, als ik dit dus morgen niet opgelost krijg, gaat vragen of ik 'harikiri' wil gaan plegen (Japanese management techniques ).

[dusty]

Wanneer wordt deze trigger uitgevoerd volgens jou?

[justmental]

Aggregatie-functies zoals count etc. geven altijd een resultaat terug, tenminste zo is het in Oracle.
Ik ken de syntax niet, maar zit je nu niet een count van de count te doen?

Overigens gooi je een 0 met quotejes in die @count, welke gedeclareerd is als int

[ Voor 45% gewijzigd door justmental op 02-01-2004 08:13 ]

[pistole]

de controle op @count lijkt me in orde, maar je selectie uit de tabel "inserted" snap ik niet. Weet je zeker dat je daar exact 1 row terugkrijgt?

[P_de_B]

Even niet ingaande op het probleem, maar een trigger is ECHT overkill voor zoiets simpels als het controleren of een naam al bestaat. Hier zijn veel betere methodes voor. Waarom zet je niet een unique constraint op de gebruikersnaam? Of desnoods een WHERE NOT EXISTS of zo in de insert code.

Triggers zijn bedoeld voor complexe validatie etc. van records, in de meeste gevallen is het overkill.

[marshal]

De trigger wordt uitgevoerd op het moment dat er gegevens aan de tabel worden toegevoegd.
Ik heb geprobeerd om dit met een unique constraint op te lossen, maar daarmee wilde ms-sql al helemaal niet meewerken.

De selectie uit inserted is de data die ingevoegd moet gaan worden in de database. daar krijg ik ook de juiste gegevens uit. dat is maar 1 rij.

de nul met quotes maakt in principe niets uit, deze is er na het optreden van het probleem pas ingezet als test.

[P_de_B]

marshal schreef op 02 januari 2004 @ 13:13:
De trigger wordt uitgevoerd op het moment dat er gegevens aan de tabel worden toegevoegd.

Dan heb je iets verkeerds gedaan. Ik denk echt dat dat wel de weg is die je moet bewandelen. Welke foutmelding kreeg je toen?

[justmental]

Probeer eens count(GebruikerID) in je debug query onderaan te zetten en kijk wat de affected rows doet.
En daarna bijvoorbeeld select @count = 1 in je gewone query.

[ Voor 163% gewijzigd door justmental op 02-01-2004 13:20 ]

[EfBe]

Nu komt het vreemde, de waarde count wordt dus 1, maar het select statement geeft 0 rijen terug.

Het select statement geeft wel rijen terug, maar aan het insert statement. Die transaction wordt teruggerold, weg rijen.

Die 0 rows affected slaat op de Insert, selects geven geen rows affected terug.

De flow van de logica door je applicatie is middels deze trigger niet meer te beheren. Je kegelt een transactie (de insert) terug in een trigger, wat echter niet de bedoeling is. P_de_B heeft het juiste antwoord: plaats een unique constraint. De insert zal dan automatisch failen wanneer de naam al bestaat.

[whoami]

Een unique constraint is inderdaad de beste oplossing.

Echter, als je toch koppig wilt zijn ( ), en het via een trigger wilt oplossen, dan doe je het best niet met een FOR ( = AFTER) trigger, maar met een INSTEAD OF trigger.

[EfBe]

... maar dan moet je de insert OOK in de trigger doen, wat IMHO neerkomt op een stored proc met een transaction

[dusty]

En dan is het geen trigger meer maar een stored procedure (wat technisch dan wel weer een nette oplossing is)

Daarnaast is de code in je trigger is compleet correct. De enige fout erin is dat het na de insert wordt uitgevoerd daardoor kan je "inserted" gebruiken. (vandaar mijn vraag wanneer jij dacht dat de trigger werdt uitgevoerd) , na de insert bestaat de username, dus wordt die username er weer uitgehaald door de trigger.

[whoami]

Er staan toch zowiezo nog wat slordigheidjes in:

• Er wordt een karakter in een INT gezet : ( select @count = '0' )
• Ik dacht dat je zowiezo beter SET gebruikt ipv SELECT om variabelen te initialiseren

[EfBe]

dusty schreef op 02 januari 2004 @ 16:26:
Daarnaast is de code in je trigger is compleet correct. De enige fout erin is dat het na de insert wordt uitgevoerd daardoor kan je "inserted" gebruiken. (vandaar mijn vraag wanneer jij dacht dat de trigger werdt uitgevoerd) , na de insert bestaat de username, dus wordt die username er weer uitgehaald door de trigger.

Ik weet dit niet zeker, maar de trigger wordt IN de insert transaction (het INSERT statement is op zichzelf een transaction, SqlServer kan GEEN nested transactions aan maar stiekum dus toch wel ) en die is niet gecommit, dus ik betwijfel of die row er al is.

Sterker, de select op de table kan wel eens deadlocken, wanneer je een non-PK field, non-index predicate gebruikt waardoor er een tablescan nodig is die... blockt op de exclusive lock van de INSERT. (speculaas, maar wellicht realiteit)

[whoami]

EfBe schreef op 02 januari 2004 @ 16:39:
[...]
Ik weet dit niet zeker, maar de trigger wordt IN de insert transaction (het INSERT statement is op zichzelf een transaction, SqlServer kan GEEN nested transactions aan maar stiekum dus toch wel ) en die is niet gecommit, dus ik betwijfel of die row er al is.

Bedoel je die row in de inserted tabel?

[dusty]

EfBe schreef op 02 januari 2004 @ 16:39:
[...]
Ik weet dit niet zeker, maar de trigger wordt IN de insert transaction (het INSERT statement is op zichzelf een transaction, SqlServer kan GEEN nested transactions aan maar stiekum dus toch wel ) en die is niet gecommit, dus ik betwijfel of die row er al is.
[..]

Dit is de Post trigger, voor de rest van de database bestaat de rij nog niet, echter voor de trigger bestaat deze wel, de insert is nog niet officieel gecommit, maar voor de "sessie" (dus eigenlijk binnen de transactie, maar zo zou ik het eigenlijk niet willen noemen binnen sql-server.)

Juist doordat het insert resultaat voor de trigger wel bestaat kan er dus makkelijk een rollback gegenereert worden, zelfs voordat de commit er echt is geweest. Het lost juist een paar "lock-up" problemen op zoals sql-server het heeft opgelost. ( wat in principe dezelfde oplossing is hoe Oracle het heeft opgelost.)

Namelijk als deze insert-rij nog niet zou bestaan voor de post trigger zouden de pre- en post- triggers niets verschillen van elkaar.

[EfBe]

whoami schreef op 02 januari 2004 @ 16:41:
[...]
Bedoel je die row in de inserted tabel?

Nee, de feitelijke table.

dusty schreef op 02 januari 2004 @ 17:41:
Dit is de Post trigger, voor de rest van de database bestaat de rij nog niet, echter voor de trigger bestaat deze wel, de insert is nog niet officieel gecommit, maar voor de "sessie" (dus eigenlijk binnen de transactie, maar zo zou ik het eigenlijk niet willen noemen binnen sql-server.)

Het zou kunnen, maar dan is die rij er ook voor anderen, alleen is die rij gelockt dmv een x-lock denk ik. SqlServer heeft geen MVCC, dus alle waarden op rows gebeurt dmv het locken van een row en dan de data te wijzigen in een transaction, wat hier dus ook gebeurt (maar details weet ik 1 2 3 niet uit mn hoofd)

Juist doordat het insert resultaat voor de trigger wel bestaat kan er dus makkelijk een rollback gegenereert worden, zelfs voordat de commit er echt is geweest. Het lost juist een paar "lock-up" problemen op zoals sql-server het heeft opgelost. ( wat in principe dezelfde oplossing is hoe Oracle het heeft opgelost.)
Namelijk als deze insert-rij nog niet zou bestaan voor de post trigger zouden de pre- en post- triggers niets verschillen van elkaar.

Nu bestaan er geen pre triggers, dus da's makkelijk
Oracle heeft het geheel anders opgelost als ik het goed heb, daar is alles echt atomic EN transparant, alle transacties op de data, in SqlServer is dat (nog) niet het geval (een reader wordt geblockt door een writer). De commit van de transaction die door INSERT bla wordt gestart vindt plaats wanneer deze trigger niet met een error wordt afgesloten. Je kunt die transaction wel 'makkelijk' terugrollen, ok, maar dat is ondoorzichtige code maken, want je executeert een INSERT en ergens wordt die transaction teruggerold, maar waar? (en dat weet je nu wellicht nog, maar over een jaar echt niet meer).

[whoami]

dusty schreef op 02 januari 2004 @ 17:41:
[...]

Dit is de Post trigger, voor de rest van de database bestaat de rij nog niet, echter voor de trigger bestaat deze wel, de insert is nog niet officieel gecommit, maar voor de "sessie" (dus eigenlijk binnen de transactie, maar zo zou ik het eigenlijk niet willen noemen binnen sql-server.)

Idd, de huidige sessie kan zijn eigen niet gecommite gegevens 'zien'.
Of andere sessies die gegevens kunnen zien, hangt af van de instelling van de transaction isolation instelling.
Als die op 'Read Uncommitted' staat, dan kunnen anderen die gegevens ook 'zien'.

[dusty]

Bij oracle (waar ik zelf het meeste mee werkt) zijn er 9 verschillende soorten triggers (dit zijn ze allemaal als ik het goed heb..).

PRE-INSERT, ON-INSERT, POST-INSERT, PRE-UPDATE, ON-UPDATE, POST-UPDATE, PRE-DELETE, ON-DELETE, POST-DELETE

Werken op "Read Uncommitted" is altijd af te raden. Ik heb het een keer bij een klant gehad die locking problemen had. Bleek dat de database op Read Uncommitted stond, resultaat was dat een andere process opeens data ging gebruiken die een milliseconde later alweer weg was, waardoor het systeem gekke kuren begon te krijgen. Persoonlijk kan ik zelfs geen een situatie bedenken waar je echt "Read Uncommitted" zou willen gebruiken.

[whoami]

dusty schreef op 02 januari 2004 @ 19:17:

Werken op "Read Uncommitted" is altijd af te raden. Ik heb het een keer bij een klant gehad die locking problemen had. Bleek dat de database op Read Uncommitted stond, resultaat was dat een andere process opeens data ging gebruiken die een milliseconde later alweer weg was, waardoor het systeem gekke kuren begon te krijgen. Persoonlijk kan ik zelfs geen een situatie bedenken waar je echt "Read Uncommitted" zou willen gebruiken.

Tuurlijk is dat af te raden; je wil gewoon niet dat iemand anders jouw niet-gecommitte data kan zien, want zoals je zelf al zegt, kan je nooit weten of die data ge-commit of gerollbacked wordt.

[justmental]

dusty schreef op 02 januari 2004 @ 19:17:
Bij oracle (waar ik zelf het meeste mee werkt) zijn er 9 verschillende soorten triggers (dit zijn ze allemaal als ik het goed heb..).

PRE-INSERT, ON-INSERT, POST-INSERT, PRE-UPDATE, ON-UPDATE, POST-UPDATE, PRE-DELETE, ON-DELETE, POST-DELETE

Buiten dat je ze zowel op statement als op row niveau hebt volgens mij wel

[marshal]

Het zal wel weer aan mij liggen, maar ik maak nu een unique constraint op die column en nu doet hij het wel.

Moet dit soort dingen ook niet om 4 uur 's nachts gaan doen, dat gaat dus alleen maar fout

Om nog even wat andere opmerkingen te beantwoorden, de rest van de projectgroep wilde niet aan stored procedures, had ik ook al gemeld dat dat beter was, maar daar wilden ze niet aan. Nogmaals, een unique constraint was mijn eerste gedachte, maar die kreeg ik dus in eerste instantie niet goed aan het werk. Waar dat aan gelegen heeft

Wat die slordigheidjes betreft, ik ben er dus een tijdje uit geweest, nu je die SET aanhaalt weet ik dat ook goed. En slordigheidjes krijg ik nu dus omdat ik een aantal dagen dus erg druk bezig ben met familie bezigheden en dan zo rond 4 uur je mail lezen en dan nog nog snel even wat op wil lossen.

[whoami]

marshal schreef op 03 januari 2004 @ 04:39:
Om nog even wat andere opmerkingen te beantwoorden, de rest van de projectgroep wilde niet aan stored procedures, had ik ook al gemeld dat dat beter was, maar daar wilden ze niet aan.

Waarom niet?
Hadden ze ook nog argumenten om ze niet te gebruiken?

[EfBe]

whoami schreef op 03 januari 2004 @ 10:26:
Waarom niet?
Hadden ze ook nog argumenten om ze niet te gebruiken?

Het is heel goed om geen stored procedures te gebruiken