December 9, 2011 at 2:19 pm
I created a procedure that organizes relevant data in a temporary table (#listos) based on a selected period. Then inserts that information into another table (ingmuestraext) to be treated differently.
The data inserted into INGMUESTRAEXT can be updated in any time by the procedure "COMPLETO". So the trigger "filtraExistencia" evaluates if an update or insert is necesary.
INGMUESTRAEXT
CREATE TABLE [dbo].[INGMUESTRAEXT](
[EXTINGNLABO] [int] NOT NULL,
[INGCOMPLETO] [int] NULL,
[INGRETIRAENV] [int] NULL,
[INGTERRENOVALOR] [int] NULL,
[INGENVASEVALOR] [int] NULL,
PRIMARY KEY CLUSTERED
(
[EXTINGNLABO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Procedure;
CREATE procedure [dbo].[completoFull] (@fecha1 datetime, @fecha2 datetime)
as
create table #listos (ingreso int,sample_number varchar (13),result varchar (22), result_text varchar (100),completo int)
insert into #listos (ingreso,sample_number,result,result_text)
select a.ingnlabo,c.sample_number,c.result,c.result_text
from ingmuestra a, samples b, sample_analysis c
where a.ingnlabo = b.lab_no and b.sample_number = c.sample_number and a.INGFECHA between @fecha1 and @fecha2
insert into #listos (ingreso)
select ingnlabo from INGMUESTRA where INGNLABO not in (select a.ingnlabo
from ingmuestra a, samples b, sample_analysis c
where a.ingnlabo = b.lab_no and b.sample_number = c.sample_number and a.INGFECHA between @fecha1 and @fecha2) and
INGFECHA between @fecha1 and @fecha2
update #listos set completo = '9'where sample_number is null
update #listos set completo = '0'where LEN (result) = 0 and LEN (result_text) = 0
update #listos set completo = '1'where LEN (result) > 0 and LEN (result_text) > 0
insert into INGMUESTRAEXT (EXTINGNLABO,INGCOMPLETO)
select distinct ingreso, completo from #listos where completo is not null
drop table #listos
Trigger
CREATE trigger [dbo].[filtraExistencia]
on [dbo].[INGMUESTRAEXT]
instead of insert
as
begin
if not exists (select w.extingnlabo
from ingmuestraext w, inserted i
where w.extingnlabo = i.extingnlabo)
insert into ingmuestraext
select i.extingnlabo, i.ingcompleto,i.ingretiraenv,i.ingterrenovalor,i.ingenvasevalor
from inserted i
else
UPDATE ingmuestraext
SET ingcompleto = i.ingcompleto,
ingretiraenv = i.ingretiraenv,
ingterrenovalor = i.ingterrenovalor,
ingenvasevalor = i.ingenvasevalor
FROM ingmuestraext a, inserted i
WHERE a.extingnlabo = i.extingnlabo
end
So when i execute de procedure i get the following error.
Mens 2627, Level 14, State 1, Procedure filtraExistencia, Line 12
Violation of PRIMARY KEY 'PK__INGMUEST__AF161A18697C9932'. Can not insert duplicate key in object 'dbo.INGMUESTRAEXT'. The duplicate key value is (85 083).
Statement has been terminated.
Doing some more testing ive come to realize de following;
- If i disable the trigger i got the same error.
- i´ve checked the sentence that inserts the data and it shows only distincts id´s
insert into INGMUESTRAEXT (EXTINGNLABO,INGCOMPLETO)
select distinct ingreso, completo from #listos where completo is not null
Result set
id state
791701
791731
791741
791761
791771
791781
791801
791811
791821
791831
December 12, 2011 at 9:43 am
Most likely you are getting duplicates in your temp table. Your primary key on the base table is EXTINGNLABO and you have two inserts into your temp table, Then you try to insert the whole temp table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply