Date parsed: 09/10/2007 00:56:46
Date: Tue, 09 Oct 2007 00:56:46 GMT

After upgrade application database from sql2000 to sql2005, some triggers
stop response or break down.
This trigger stop at the if statement in sql2005 , but at sql 2000 work fine:
--SQL command:
Delete From DDES400..T400TITC Where cd_cli = 47594619. And cd_ag = 122. And
cd_cta_cor = 5239.
--trigger code
alter TRIGGER TR_TITC_DEL ON DBO.T400TITC
FOR DELETE
AS
DECLARE @SQ_DEL DECIMAL(10),
@CD_USU CHAR(10),
@CD_UND NUMERIC(3),
@CD_CLI NUMERIC(10),
@NR_OPE CHAR(20),
@DT_ENC DATETIME,
@DT_CAD DATETIME,
@ID_SOL CHAR(1),
@SQ_TIT INT,
@LI_RET INT
-- VERIFICA SE O REGISTRO JÁ EXISTE NA DELE, SE JÁ EXISTIR, NÃO FAZ NADA
IF (SELECT count(*) FROM T400DELE, DELETED, T400CTAD WHERE SG_TAB = 'CT'
AND CH_TAB = CONVERT(CHAR(8) , DELETED.CD_CLI) + ';' + CONVERT(CHAR(4),
DELETED.CD_AG) + ';' + CONVERT(CHAR(5), DELETED.CD_CTA_COR) + ';' + CONVERT
(CHAR(1), T400CTAD.DV_CTA_COR)) = 0

--Exactly in "IF" statement, the code stop response.

Any help!

Krisnamourt

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1

Date parsed: 09/10/2007 22:15:11
Date: Tue, 09 Oct 2007 22:15:11 GMT

Ok. The is that:
CREATE TRIGGER TR_TITC_DEL ON DBO.T400TITC
FOR DELETE
AS
DECLARE @SQ_DEL DECIMAL(10),
@CD_USU CHAR(10),
@CD_UND NUMERIC(3),
@CD_CLI NUMERIC(10),
@NR_OPE CHAR(20),
@DT_ENC DATETIME,
@DT_CAD DATETIME,
@ID_SOL CHAR(1),
@SQ_TIT INT,
@LI_RET INT
-- VERIFICA SE O REGISTRO JÁ EXISTE NA DELE, SE JÁ EXISTIR, NÃO FAZ NADA

-- HERE IS THE PROBLEM !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!
IF (SELECT count(*) FROM T400DELE, DELETED, T400CTAD WHERE SG_TAB = 'CT'
AND CH_TAB = CONVERT(CHAR(8) , DELETED.CD_CLI) + ';' + CONVERT(CHAR(4),

DELETED.CD_AG) + ';' + CONVERT(CHAR(5), DELETED.CD_CTA_COR) + ';' + CONVERT
(CHAR(1), T400CTAD.DV_CTA_COR)) = 0
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
BEGIN
SELECT @SQ_DEL = ISNULL(MAX(SQ_DEL), 0) + 1 FROM T400DELE

INSERT INTO T400DELE
(SQ_DEL,
CH_TAB,
SG_TAB,
DH_ATU,
ID_SIC)
SELECT @SQ_DEL,
CONVERT(CHAR(8) , DELETED.CD_CLI) + ';' + CONVERT(CHAR(4), DELETED.CD_AG) +
';' + CONVERT(CHAR(5), DELETED.CD_CTA_COR) + ';' + CONVERT(CHAR(1), T400CTAD.
DV_CTA_COR) ,
'CT',
GETDATE(),
'S'
FROM DELETED,T400CTAD
WHERE DELETED.CD_AG = T400CTAD.CD_AG
AND DELETED.CD_CTA_COR = T400CTAD.CD_CTA_COR
END

-- VERIFICA SE O REGISTRO JÁ EXISTE NA CEXC, SE JÁ EXISTIR, NÃO FAZ NADA
IF (SELECT COUNT(*) FROM T400CEXC, DELETED
WHERE T400CEXC.CD_AG = DELETED.CD_AG AND T400CEXC.CD_CLI = DELETED.CD_CLI
AND T400CEXC.CD_CTA_COR = DELETED.CD_CTA_COR ) = 0
BEGIN
-- Insere na T400CEXC
INSERT INTO T400CEXC
(CD_AG,
CD_CLI,
CD_CTA_COR,
DV_CTA_COR,
CD_CLI_SCD,
DH_ATU)
select deleted.CD_AG,
deleted.CD_CLI,
deleted.CD_CTA_COR,
CONVERT(CHAR(1),T400CTAD.DV_CTA_COR),
T400PESS.CD_CLI_SCD,
GETDATE()
from deleted, t400ctad, t400pess
where (deleted.cd_ag = t400ctad.cd_ag) and
(deleted.cd_cta_cor = t400ctad.cd_cta_cor) and
(deleted.cd_cli = t400pess.cd_cli)
END

---

---

-- ATUALIZAÇÃO DA TABELA T400CNTA

---

---

-- IF @@ROWCOUNT > 0
-- BEGIN
SELECT @NR_OPE = SUBSTRING(REPLICATE('0', 10 - LEN(RTRIM(CONVERT(CHAR(5),
T400CTAD.CD_CTA_COR)) + RTRIM(CONVERT(CHAR(5), T400CTAD.DV_CTA_COR)))) +
RTRIM(CONVERT(CHAR(5), T400CTAD.CD_CTA_COR)) + RTRIM(CONVERT(CHAR(5),
T400CTAD.DV_CTA_COR)), 1, 10),
@CD_USU = DELETED.CD_USU_ATU,
@CD_CLI = DELETED.CD_CLI,
@ID_SOL = T400CTAD.CD_TP_CTA,
@CD_UND = DELETED.CD_AG,
@DT_ENC = GETDATE()
FROM DELETED,T400CTAD
WHERE DELETED.CD_AG = T400CTAD.CD_AG AND
DELETED.CD_CTA_COR = T400CTAD.CD_CTA_COR


IF @@ERROR <> 0
BEGIN
RAISERROR ('TR_TITC_DEL: ERRO NA ATUALIZAÇÃO DE EXCLUSÃO DA CONTA DO
CLIENTE (T400CNTA)- 1', 1, 2)
END

IF (@NR_OPE IS NOT NULL) AND (LEN(@NR_OPE) > 0)
BEGIN
EXEC PR_ATUALIZA_CNTA_V2 @CD_UND, @CD_CLI, 'CC', 'CC', 'C', @NR_OPE,
@ID_SOL, @DT_CAD, @DT_ENC, 'D', @CD_USU, @DT_ENC, @SQ_TIT, @LI_RET OUT
IF @LI_RET <> 0
BEGIN
RAISERROR ('TR_TITC_DEL: ERRO NA ATUALIZAÇÃO DE EXCLUSÃO DA CONTA DO
CLIENTE (T400CNTA)- 2', 1, 2)
END
END
-- END


Hugo Kornelis wrote:
>>After upgrade application database from sql2000 to sql2005, some triggers
>>stop response or break down.
>[quoted text clipped - 23 lines]
>>
>>--Exactly in "IF" statement, the code stop response.
>
>Hi Krisnamourt,
>
>Can you please post the full trigger code? It would also help to see the
>table structure (as CREATE TABLE statement, with all constraints,
>indexes, and properties included), plus some sample data (posted as
>INSERT statements) that we can use to reproduce the issue.
>

--
Message posted via http://www.sqlmonster.com

Date parsed: 19/10/2007 02:44:28
Date: Fri, 19 Oct 2007 00:44:28 +0200

On Tue, 09 Oct 2007 22:15:11 GMT, Krisnamourt via SQLMonster.com wrote:

>Ok. The is that:
(snip)

Hi Krisnamourt,

Sorry for the delay in replying.

I see no obvious issues with your code that would cause the trigger to
stop execution. So I'll have to repeat my previous request to post a
full repro - that is, CREATE TABLE and INSERT statements to set up the
test data, plus the DELETE statement that causes the trigger to
misbehave.

I do see various other potential problems with your trigger, but since I
can't understand the comments (Spanish? Portuguese?) and since I have no
idea about your table structure, most of the below is just guesswork,
and most is probably unrelated to your problem.

- The huge amount of variables suggests that you have written a trigger
that only handles single-row deletes. A trigger should handle multi-row
deletes as well.

- The CH_TAB appears to contain a concatenation of several different
data points. Please consider normalising your table. To be more precise,
do not combine multiple attributes in a single column.

- The cryptic five-letter column and variable names and three-number
plus five-letter table names don't work towards making your code
readable. If you give them descriptive names, it probably won't help me
much, but it will help other people who speak the same language you do.

- On a related note, using only upper case makes the code less readable
as well. I personally prefer uppercase for KEYWORDS; camel case for
TableNames, ColumnNames, and VariableNames; and lowercase for data
types. Feel free to use another method, but do stick to your choice once
you've made one.

- Try to learn the "infixed" join syntax instead of the old-fashioned
syntax with a comma-seperated list of tables and all join criteria in
the WHERE clause. It may take some getting used to, but it makes for
much better documented code.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis