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.