asked on

INSERT AND UPDATE WITH THE SAME TRIGGER

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 ?

ASKER CERTIFIED SOLUTION

Chris Ashcraft

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pabilio

ASKER

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.

Pabilio

ASKER

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.