INSERT AND UPDATE WITH THE SAME TRIGGER

Pabilio
Pabilio used Ask the Experts™
on
Hi,

I have the following Trigger:


CREATE TRIGGER RETEN_IVA
ON
SACOMP
FOR
INSERT
AS

INSERT INTO sacomp (TIPOCOM, NUMEROD, CODPROV, DESCRIP, SIGNO, RETENIVA, CODOPER, FACTAFECTA, FECHAE)
SELECT 'H' AS TIPOCOM, NUMEROR AS NUMEROD, CODPROV, DESCRIP, SIGNO, RETENIVA, CODOPER, NUMEROD AS FACTAFECTA, GETDATE() FROM INSERTED
WHERE NOTAS2 ='S'

UPDATE SACOMP
SET SACOMP.NUMEROR ='',
SACOMP.RETENIVA = 0,
SACOMP.NOTAS2=''
FROM SACOMP
WHERE NOTAS2 = 'S'

And I got an error saying that It can insert a null Value in primary key...
The primary key is NUMEROD...

Could you help me ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Analyst - Technology
Commented:
Can you post the INSERT statement that you are using? It appears that a null value is getting  inserted into the sacomp.NUMEROR field and the insert statement in the trigger is trying to put the null NUMEROR into NUMEROD

Author

Commented:
Hi Micropc,

I can't post the insert statement due that I'm working with a closed software.

But I think you'r right that the solution go that way... I'm going to try to obtain numeror from a different table.

I'll let you know if this works...

Thank you for your time.
Roberto.

Author

Commented:
The combination of:

1)
USE [CEDRO]
GO
/****** Objeto:  Trigger [dbo].[RETEN_IVA]    Fecha de la secuencia de comandos: 04/17/2012 22:54:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[RETEN_IVA]
ON
[dbo].[SACOMP]
FOR
INSERT
AS


INSERT INTO sacomp (TIPOCOM, NUMEROD, CODPROV, DESCRIP, SIGNO, RETENIVA, CODOPER, FACTAFECTA, FECHAE, NUMEROR, ID3,NROCTROL,CODUSUA, NOTAS1)
SELECT 'H' AS TIPOCOM, NUMEROD+' R', CODPROV, DESCRIP, SIGNO, RETENIVA, CODOPER, NUMEROD AS FACTAFECTA, FECHAE, (SELECT '0000'+(CONVERT (VARCHAR (8),PRXRETENIVA)) FROM SACONF), ID3, NROCTROL, CODUSUA, NOTAS1  FROM INSERTED
WHERE NOTAS2 LIKE '%S%'

UPDATE SACOMP
SET
SACOMP.RETENIVA = 0
FROM SACOMP
WHERE NOTAS2 LIKE '%S%'

and
2)
USE [CEDRO]
GO
/****** Objeto:  Trigger [dbo].[UPD_RET]    Fecha de la secuencia de comandos: 04/17/2012 22:56:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[UPD_RET]
ON [dbo].[SACOMP]
FOR UPDATE
AS

UPDATE SACOMP
SET NUMEROR=''
WHERE RETENIVA = 0


UPDATE SACOMP
SET NUMEROR= substring (CONVERT(VARCHAR (8),fechae,112),1,6)+numeror
where numeror is not null and len(numeror)=8

UPDATE SACOMP
SET NOTAS2=''
WHERE NOTAS2 = 'S'

Does the trick...

The problem was with the insert as you posted.

Thanks for the help.
Roberto.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial