Trigger ORACLE to SQL 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger ORACLE to SQL 2005

Author

Topic

Sergio_Costa
Starting Member

5 Posts

Posted - 2010-07-12 : 16:02:50

Friends, good afternoon (here in Brazil)! Have to migrate down the trigger oracle to sql server - use 2005 - the goal is to restrict the trigger insert records from a table, the information system (warehousing) - ERP - Company I work. The process is: restrict insertion of the same person twice. Pleasure to join this forum!
Trigger>
CREATE OR REPLACE TRIGGER TRG_DEDUP
BEFORE
INSERT ON PPESSOA
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
CURSOR CPESSOA (PLIMIAR FLOAT) IS
SELECT PPESSOA .*
Regexp_replace (
TRIM (UPPER (TRIM (NAME) | | '' | | TRIM (dtnascimento) | | '' | | TRIM (STREET) | | '' | | TRIM (NUMBER) | | '' | |
TRIM (NEIGHBORHOOD) | | '' | | TRIM (CITY) | | '' | | TRIM (CEP) | | '' | | TRIM (Telephone1) | | '' | |
TRIM (SEX) | | '' | | TRIM (ESTADOCIVIL) )),'() (2 ,}',' ') REG
FROM PPESSOA
UTL_MATCH.JARO_WINKLER WHERE (TRIM (UPPER (NAME)), TRIM (UPPER (regexp_replace (: NEW.NOME, '() 2 (,}',''))))>= PLIMIAR OR
street =: NEW.RUA OR
district =: NEW.bairro OR
cep =: NEW.CEP OR
cpf =: NEW.CPF OR
Telephone1 =: NEW.telefone1 OR
cartidentidade =: NEW.cartidentidade;

CURSOR CPESSOA2 (PLIMIAR FLOAT) IS
SELECT PPESSOA .*
FROM PPESSOA
UTL_MATCH.JARO_WINKLER WHERE (TRIM (UPPER (NAME)), TRIM (UPPER (regexp_replace (: NEW.NOME, '() 2 (,}',''))))>= PLIMIAR
DTNASCIMENTO AND IS NOT NULL;

NOVOREG VARCHAR2 (380): =
TRIM (UPPER (TRIM (: NEW.NOME) | | '' | | TRIM (: NEW.dtnascimento) | | '' | | TRIM (: NEW.RUA) | | '' | | TRIM (: NEW.NUMERO) | | '' | |
TRIM (: NEW.BAIRRO) | | '' | | TRIM (: NEW.CIDADE) | | '' | | TRIM (: NEW.CEP) | | '' | | TRIM (: NEW.TELEFONE1) | | '' | |
TRIM (: NEW.SEXO) | | '' | | TRIM (: NEW.ESTADOCIVIL)));
REST OF VARCHAR2 (1000): = TRIM (: NEW.apelido | |: NEW.estadocivil | |: NEW.sexo | |: NEW.nacionalidade | |: NEW.grauinstrucao | |: NEW.rua | |
: NEW.numero | |: NEW.complemento | |: NEW.bairro | |: NEW.estado | |: NEW.cidade | |: NEW.cep | |: NEW.pais | |
: NEW.regprofissional | |: NEW.cpf | |: NEW.telefone1 | |: NEW.cartidentidade | |: NEW.tituloeleitor);

THRESHOLD FLOAT: = 0.85; - THRESHOLD CUT
I INTEGER;
J INTEGER;
QTY INTEGER;
FLAG INTEGER;
SA VARCHAR2 (140);
SB VARCHAR2 (140);
YES FLOAT: = 0;
VS FLOAT;
IN FLOAT;
NB FLOAT;
TLREG TYPE IS TABLE OF VARCHAR2 (200);
LREG TLREG: = TLREG (NULL);
StrErr VARCHAR2 (1000): ='';

BEGIN
- STEP 1: IDENTIFY IF EXIST RG OR CPF JA
if (: new.cpf is not null) THEN
SELECT COUNT (*) INTO BYTES FROM WHERE PPESSOA CPF =: NEW.CPF;
IF (QTY> 0) THEN
RAISE_APPLICATION_ERROR (-20000, 'ERROR: IMPOSSIBLE REGISTER PERSON, BECAUSE THERE IS A PERSON JA ALREADY REGISTERED WITH THIS SFC', false);
END IF;
END IF;
if (: new.cartidentidade is not null) THEN
SELECT COUNT (*) INTO BYTES FROM WHERE PPESSOA cartidentidade =: NEW.cartidentidade;
IF (QTY> 0) THEN
RAISE_APPLICATION_ERROR (-20000, 'ERROR: IMPOSSIBLE REGISTER PERSON, BECAUSE THERE IS A PERSON JA ALREADY REGISTERED WITH THIS RG', false);
END IF;
END IF;

- COMES THE CASE OF NAME + nome
IF ((TRIM (: NEW.NOME) IS NOT NULL) AND (TRIM (: NEW.dtnascimento) IS NOT NULL) AND (REST IS NULL)) THEN
FOR X IN CPESSOA2 (0.96) LOOP
IF (X = DTNASCIMENTO: NEW.DTNASCIMENTO) THEN
StrErr: CODE = X | | '-' | | X. NAME | | '-' | | X. DTNASCIMENTO;
RAISE_APPLICATION_ERROR (-20000, 'ERROR: IMPOSSIBLE registering people, because it already exists! RECORD LIKE:' | |
CHR (10) | | strErr, TRUE);
END IF;
END LOOP;
END IF;

- GERA TOKENS OF NEW REG
NOVOREG: = regexp_replace (NOVOREG, '() 2 (,}','');
J: = 1;
LREG.TRIM;
LOOP
SB: = GET_TOKEN (NOVOREG, J);
SB EXIT WHEN IS NULL;
LREG.EXTEND;
LREG (J): = SB;
J: = J +1;
END LOOP;

- 2 PERSON: SIMILARITY (CoSine Similarity) TO GET MORE LIKE THE RECORD
FOR X IN CPESSOA (THRESHOLD) LOOP - FOR EACH SELECTED TO BE COMPARED WITH THE NEW RECORD
YES: = 0;
I: = 1;
LOOP - LOOP DE REG
SA: = GET_TOKEN (X REG, I);
EXIT WHEN SA IS NULL;
FLAG: = 0;
FOR J IN LREG.FIRST .. LREG.LAST LOOP - LOOP DE NOVOREG
SB: = LREG (J);
IF (SA <> SB)
THEN VS UTL_MATCH.JARO_WINKLER = (SA, SB);
ELSE VS: = 1.0; FLAG: = 1;
END IF;
IF VS> = 0.83 THEN
SIM: SIM + = VS - FLAG: = 1;
END IF;
EXIT WHEN FLAG = 1;
END LOOP;
I: = I +1;
END LOOP;
NA: = SQRT (I-1);
NB: = SQRT (LREG.COUNT);
YES: = YES / (NA * NB);

DBMS_OUTPUT.PUT_LINE ('SIMILARITY BETWEEN'''|| NOVOREG | |''' AND X.'''|| REG | |'''EH' | | SIM);

IF YES> = / * THRESHOLD * / 0.95 THEN
StrErr: strErr = | | X CODE | | '-' | | X. REG | | '(' | | TO_CHAR (SIM, '0 .999 ') ||')'|| chr (10);
END IF;

END LOOP; - CPESSOA

IF LENGTH (strErr)> 0 THEN
RAISE_APPLICATION_ERROR (-20000, 'ERROR: IMPOSSIBLE registering people, because it already exists! RECORD (S) LIKE (ES ):'||
CHR (10) | | strErr, TRUE);
END IF;

END;
/
ALTER TRIGGER DISABLE TRG_DEDUP
/

Sérgio Costa.
Consultant Information Systens

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-12 : 16:10:39

bom dia! first my condolences for Brazil exiting early from World Cup :(
ok you are saying restrict insertion of the same person twice. In what tables? what fields constitute same person? First Name + Last name or is there more?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Sergio_Costa
Starting Member

5 Posts

Posted - 2010-07-12 : 16:27:25

Dear Yosiasz!
Good morning! Yeah, we had to leave for stupidity ... What is your country? Exactly, restrict insertion of the same person. No bank has PPESSOA table, the main fields are: name, birth date, street number (house), neighborhood, city, zip code, phone, sex, marital status, two most important fields are: rg (general registration) and cpf (individual record). Together these form fields to identify the person.

Greetings!

Sérgio Costa.
Consultant Information Systens

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-12 : 16:48:13

Sergio

1. What do you mean by No bank has PPESSOA table?
2. Which fields identify the person again? name, birth date, street number (house), neighborhood, city, zip code, phone, sex, marital status, rg (general registration) + cpf (individual record).

I was hoping Brazil would win

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-12 : 17:05:57

create a unique key or index instead of a trigger

Sergio_Costa
Starting Member

5 Posts

Posted - 2010-07-12 : 17:18:35

Dear Yosiasz

Answers:
1) table name is PPESSOA
2) the fields are:
NOME, DTNASCIMENTO, RUA, NUMERO, BAIRRO, CIDADE, CEP, TELEFONE1, SEXO, ESTADOCIVIL, '(){2,}',' '

The cursors add the fields above. This checking is similarity.

After the "IF" are checked:

a) If there are people with the same values of the fields "RG" and "CPF";
b) verify there are people with same name and date of birth;
c) checks similarity;
...

Sérgio Costa.
Consultant Information Systens

Sergio_Costa
Starting Member

5 Posts

Posted - 2010-07-12 : 17:20:58

Dear Russell! I would like the key or index, considering my background (I have the trigger)?

Sérgio Costa.
Consultant Information Systens

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-12 : 17:52:55

Create Unique Index ux_PPESSOA_person On PPESSOA (fields that define a unique customer here)

for example:

Create Unique Index ux_PPESSOA_person On PPESSOA (
  NOME, DTNASCIMENTO, RUA, NUMERO, BAIRRO
)

the fields I listed are just an example. YOU need to determine what makes an individual unique.

Hope this helps

Sergio_Costa
Starting Member

5 Posts

Posted - 2010-07-12 : 21:03:55

Dear Yosiasz! Think you can migrate to sql trigger?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-13 : 12:22:22

follow what russel is suggesting, put all of the fields that makes an individual unique

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources