[ Foro de SQL (y MySQL) ]

Triggers Cambio

11-Jun-2019 23:56
Invitado (George )
0 Respuestas


Buenas Tardes

Presento la siguiente situación. El Triggers en la actualidad funciona pero deseo agregarle la funcionalidad de que me me refleja el mensaje '@?@1#USR_DISOPD# ya que la cantidad de items no puede ser mayor a 3#' pero que solo lo refleja si están llenos los 3 renglones de lo contrario no dejar de cargar algún dato. Es decir si deseo caragar 1 o 2 renglones no me debería permitir. Anexo Query

USE [GEORGE]
GO
/****** Object: Trigger [dbo].[InsUSR_DISOPD] Script Date: 06/11/2019 17:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** CWA Object: Insert trigger for table USR_DISOPD - Script Date: 12/12/17 11:24:19 ******/
ALTER TRIGGER [dbo].[InsUSR_DISOPD] ON [dbo].[USR_DISOPD]
FOR INSERT
AS
Begin
/* BEGIN CWA SECTION - DO NOT TOUCH */
Declare
@numrows int,
@numnull int,
@PKValues varchar(255),
@FKFields varchar(255),
@USR_DISOPD_PERIOD numeric(6,0) ,
@USR_DISOPD_CODREG varchar(6) ,
@USR_DISOPD_NUMDIA smallint ,
@cwaerrmsg varchar(255),
@cwaerr int

Select @numrows=@@rowcount
if @numrows=0
return


Select @cwaerr=0
if update(USR_DISOPD_PERIOD) Or
update(USR_DISOPD_CODREG)
begin
if (Select count(*)
From USR_DISOPI,inserted
Where (USR_DISOPI.USR_DISOPI_PERIOD =inserted.USR_DISOPD_PERIOD) and
(USR_DISOPI.USR_DISOPI_CODREG =inserted.USR_DISOPD_CODREG) ) != @numrows
begin
Select @cwaerr=1
Declare cur Insensitive Cursor
for Select inserted.USR_DISOPD_PERIOD, inserted.USR_DISOPD_CODREG, inserted.USR_DISOPD_NUMDIA
from inserted
where not exists (Select * From USR_DISOPI where (USR_DISOPI.USR_DISOPI_PERIOD =inserted.USR_DISOPD_PERIOD) and
(USR_DISOPI.USR_DISOPI_CODREG =inserted.USR_DISOPD_CODREG) )
for Read Only
Open cur
fetch next from cur into @USR_DISOPD_PERIOD, @USR_DISOPD_CODREG, @USR_DISOPD_NUMDIA
if @@fetch_status=0
begin
Select @PKValues = convert(varchar(255),@USR_DISOPD_PERIOD) + ',' +convert(varchar(255),@USR_DISOPD_CODREG) + ',' +convert(varchar(255),@USR_DISOPD_NUMDIA)
Select @cwaerrmsg='@?@1#USR_DISOPD#' + @PKValues + '#USR_DISOPD_PERIOD, USR_DISOPD_CODREG#'
Raiserror(@cwaerrmsg, 16,-1)
fetch next from cur into @USR_DISOPD_PERIOD, @USR_DISOPD_CODREG, @USR_DISOPD_NUMDIA
end
Deallocate cur
end
end

if @cwaerr=1
Rollback Transaction

/* END CWA SECTION */

/* BEGIN Usuario TRIGGER SECTION */
UPDATE B
SET B.USR_DISOPD_SALDIA = B.USR_DISOPD_IMPDIA
FROM USR_DISOPI,INSERTED A,USR_DISOPD B
WHERE USR_DISOPI_PERIOD = B.USR_DISOPD_PERIOD
AND USR_DISOPI_CODREG = B.USR_DISOPD_CODREG
AND USR_DISOPI_PERIOD = A.USR_DISOPD_PERIOD
AND USR_DISOPI_CODREG = A.USR_DISOPD_CODREG
AND B.USR_DISOPD_NUMDIA = A.USR_DISOPD_NUMDIA

DECLARE @PERIOD AS VARCHAR(6),
@CODREG AS VARCHAR(15),
@IMPTOT AS NUMERIC(18,2),
@NROITM AS INT,
@SALDIF AS NUMERIC(18,2),
@IMPDIA AS NUMERIC(18,2)

SELECT @PERIOD = USR_DISOPD_PERIOD,
@CODREG = USR_DISOPD_CODREG,
@NROITM = USR_DISOPD_NROITM
FROM INSERTED




SELECT @IMPTOT = SUM(USR_DISOPD_IMPDIA)
FROM USR_DISOPD
WHERE USR_DISOPD_PERIOD = @PERIOD
AND USR_DISOPD_CODREG = @CODREG
AND @NROITM = 3

SELECT @SALDIF = USR_DISOPI_IMPDIF
FROM USR_DISOPI
WHERE USR_DISOPI_PERIOD = @PERIOD
AND USR_DISOPI_CODREG = @CODREG

SELECT @IMPDIA = SUM(USR_DISOPD_IMPDIA)
FROM INSERTED
WHERE USR_DISOPD_PERIOD = @PERIOD
AND USR_DISOPD_CODREG = @CODREG

IF (@IMPTOT <> @SALDIF AND @NROITM = 3)
BEGIN
SET @cwaerrmsg = '@?@1#USR_DISOPD# ya que el importe debe ser igual al total diferido #'
SET @CWAERR = 1
RAISERROR (@cwaerrmsg,16,- 1)
END

IF @NROITM > 3
BEGIN
SET @cwaerrmsg = '@?@1#USR_DISOPD# ya que la cantidad de items no puede ser mayor a 3#'
SET @CWAERR = 1
RAISERROR (@cwaerrmsg,16,- 1)
END


IF @CWAERR = 1
ROLLBACK TRANSACTION
/* END Usuario TRIGGER SECTION */

end




Si ya eres usuario del sistema, puedes contestar desde tu cuenta y así ganar prestigio.

Si sólo eres un visitante, puedes optar por...