[MSSQL] Wyzwalacz, pesel

0

Cześć :)
Mam do wykonania takie zapytanie

Napisz wyzwalacz, który umożliwi dodanie 
do bazy osobę z poprawnym numerem 
pesel.

create table osoby
(
id int identity(1,1) not null,
nazwisko varchar(40) not null,
data_urodzenia datetime not null,
pesel varchar(11)
) 

Możecie coś podpowiedzieć jak się za to zabrać?

0
  1. Tworzysz triggera na insert:

CREATE TRIGGER nazwa ON tabelka
FOR INSERT
AS
...

  1. W triggerze weryfikujesz numer pesel. Możesz sprawdzić jego wartość w tabelce tymczasowej inserted.

Sprawdzanie
http://pl.wikipedia.org/wiki/PESEL#Cyfra_kontrolna_i_sprawdzanie_poprawno.C5.9Bci_numeru

  1. Jeśli numer jest nieprawidłowy rzucasz exception. Pamiętaj że pesel może być null według definicji.

  2. Przydał by się jeszcze trigger na instead of update, żeby nie można było wprowadzić błędnego na updacie.

0

Do takiego czegoś zamiast triggera lepszy byłby zwykły check constraint (z własną funkcją).

0

Mam coś takiego, natomiast wprowadzając wprowadzając błędny pesel (44051401358) mimo to go dodaje.

create trigger s_pesel on osoby
for insert
as
declare @pesel varchar(11)
if 
((((cast(substring(@pesel,1,1) as tinyint)*9)
+(cast(substring(@pesel,2,1) as bigint)*7)
+(cast(substring(@pesel,3,1) as bigint)*3)
+(cast(substring(@pesel,4,1) as bigint)*1)
+(cast(substring(@pesel,5,1) as bigint)*9)
+(cast(substring(@pesel,6,1) as bigint)*7)
+(cast(substring(@pesel,7,1) as bigint)*3)
+(cast(substring(@pesel,8,1) as bigint)*1)
+(cast(substring(@pesel,9,1) as bigint)*9)
+(cast(substring(@pesel,10,1) as bigint)*7))%10)=0 
and (cast(substring(@pesel,11,1) as bigint)*1)=0)
or
 10-((cast(substring(@pesel,1,1) as tinyint)*9)
+(cast(substring(@pesel,2,1) as bigint)*7)
+(cast(substring(@pesel,3,1) as bigint)*3)
+(cast(substring(@pesel,4,1) as bigint)*1)
+(cast(substring(@pesel,5,1) as bigint)*9)
+(cast(substring(@pesel,6,1) as bigint)*7)
+(cast(substring(@pesel,7,1) as bigint)*3)
+(cast(substring(@pesel,8,1) as bigint)*1)
+(cast(substring(@pesel,9,1) as bigint)*9)
+(cast(substring(@pesel,10,1) as bigint)*7))%10=(cast(substring(@pesel,11,1) as bigint))
rollback 
0

Jak rozumiem, prowadzący wymaga koniecznie triggera, trudno.
W Twoim triggerze nie ma w ogóle odniesienia do aktualnie modyfikowanego rekordu. Operujesz na nowo utworzonej zmiennej @pesel, która jest pusta, nie na wprowadzonym peselu. Poza tym suma kontrolna to nie wszystko - trzeba jeszcze sprawdzić datę urodzenia.

0

Mógłbym prosić o jakieś bardziej konkretne rady, nie mam pojęcia jak to dalej ruszyć ...

0

Po

DECLARE @pesel VARCHAR(11)

dajSET @pesel = NEW.pesel

0

The multi-part identifier "new.pesel" could not be bound.

0

Prawie tak jak @Marcin.Miga:

SELECT @pesel = pesel FROM Inserted
0

Ok, prawie działa, natomiast sprawa wygląda tak, że teraz dobre pesele odrzuca a złe dopisuje.

create trigger s_pesel on osoby
for insert
as
declare @pesel varchar(11)
SELECT @pesel=pesel FROM Inserted
if 
( (((cast(substring(@pesel,1,1) as tinyint)*9)
+(cast(substring(@pesel,2,1) as bigint)*7)
+(cast(substring(@pesel,3,1) as bigint)*3)
+(cast(substring(@pesel,4,1) as bigint)*1)
+(cast(substring(@pesel,5,1) as bigint)*9)
+(cast(substring(@pesel,6,1) as bigint)*7)
+(cast(substring(@pesel,7,1) as bigint)*3)
+(cast(substring(@pesel,8,1) as bigint)*1)
+(cast(substring(@pesel,9,1) as bigint)*9)
+(cast(substring(@pesel,10,1) as bigint)*7))%10)=0 
and (cast(substring(@pesel,11,1) as bigint)*1)=0)
or
 10-((cast(substring(@pesel,1,1) as tinyint)*9)
+(cast(substring(@pesel,2,1) as bigint)*7)
+(cast(substring(@pesel,3,1) as bigint)*3)
+(cast(substring(@pesel,4,1) as bigint)*1)
+(cast(substring(@pesel,5,1) as bigint)*9)
+(cast(substring(@pesel,6,1) as bigint)*7)
+(cast(substring(@pesel,7,1) as bigint)*3)
+(cast(substring(@pesel,8,1) as bigint)*1)
+(cast(substring(@pesel,9,1) as bigint)*9)
+(cast(substring(@pesel,10,1) as bigint)*7))%10=(cast(substring(@pesel,11,1) as bigint))
rollback
1
Zbychersky napisał(a):

Ok, prawie działa, natomiast sprawa wygląda tak, że teraz dobre pesele odrzuca a złe dopisuje.

No to teraz proszę pomyśl - jeśli przy warunkach poprawnego numeru pesel (%10=0 lub %10=ostatnia cyfra) robisz rollback, to jak to ma działać?

0

Zmieniajac na COMMIT moge wpisać każdy pesel.

0

Tak z ciekawości - gdzie masz takie zadanie? Która szkoła, jaki przedmiot?

0

UE w Katowicach,przedmiot bazy danych. Kilka osób się już u nas na roku nad tym głowi po "bardzo rozbudowanym" wejściu do funkcji,procedur i wyzwalaczy

0

Zadanie musimy zrobić do dzisiaj do 24. Przeczytałem trochę 'internetów' na temat wyzwalaczy ale nadal nic mi mądrego nie przychodzi do głowy.

CREATE TRIGGER s_pesel ON osoby
FOR INSERT, UPDATE
AS
DECLARE @pesel VARCHAR(11)
SELECT @pesel=pesel FROM Inserted
IF
( (((CAST(SUBSTRING(@pesel,1,1) AS tinyint)*9)
+(CAST(SUBSTRING(@pesel,2,1) AS BIGINT)*7)
+(CAST(SUBSTRING(@pesel,3,1) AS BIGINT)*3)
+(CAST(SUBSTRING(@pesel,4,1) AS BIGINT)*1)
+(CAST(SUBSTRING(@pesel,5,1) AS BIGINT)*9)
+(CAST(SUBSTRING(@pesel,6,1) AS BIGINT)*7)
+(CAST(SUBSTRING(@pesel,7,1) AS BIGINT)*3)
+(CAST(SUBSTRING(@pesel,8,1) AS BIGINT)*1)
+(CAST(SUBSTRING(@pesel,9,1) AS BIGINT)*9)
+(CAST(SUBSTRING(@pesel,10,1) AS BIGINT)*7))%10)=0 
AND (CAST(SUBSTRING(@pesel,11,1) AS BIGINT)*1)=0)
OR
 (10-((CAST(SUBSTRING(@pesel,1,1) AS tinyint)*9)
+(CAST(SUBSTRING(@pesel,2,1) AS BIGINT)*7)
+(CAST(SUBSTRING(@pesel,3,1) AS BIGINT)*3)
+(CAST(SUBSTRING(@pesel,4,1) AS BIGINT)*1)
+(CAST(SUBSTRING(@pesel,5,1) AS BIGINT)*9)
+(CAST(SUBSTRING(@pesel,6,1) AS BIGINT)*7)
+(CAST(SUBSTRING(@pesel,7,1) AS BIGINT)*3)
+(CAST(SUBSTRING(@pesel,8,1) AS BIGINT)*1)
+(CAST(SUBSTRING(@pesel,9,1) AS BIGINT)*9)
+(CAST(SUBSTRING(@pesel,10,1) AS BIGINT)*7))%10)
=(CAST(SUBSTRING(@pesel,11,1) AS BIGINT))
BEGIN
PRINT 'Wprowadzony pesel jest poprawny!'
END
ELSE
BEGIN
PRINT 'Wprowadzono niepoprawny pesel!'
ROLLBACK
END

Przy takim kodzie, raz to działa a raz nie.

1 użytkowników online, w tym zalogowanych: 0, gości: 1