June 1, 2011 at 10:04 am
Hi,
I have the following procedure:
ALTER proc [dbo].[spSGCT_ALTERACARTOESTEMP]
as
declare @var1 varchar(8000)
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TRG_INS_CARTOES]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TRG_INS_CARTOES]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CARTOES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CARTOES]
EXEC sp_rename 'CARTOESTEMP', 'CARTOES'
ALTER TABLE [dbo].[CARTOES] WITH NOCHECK ADD
CONSTRAINT [PK_CARTOES] PRIMARY KEY CLUSTERED
(
[COD_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE INDEX [IX_CARTOES_NIF_ANTIGO] ON [dbo].[CARTOES]([NIF_ANTIGO]) WITH FILLFACTOR = 90 ON [PRIMARY]
set @var1 = 'CREATE TRIGGER TRG_INS_CARTOES ON dbo.CARTOES
INSTEAD OF INSERT
AS
begin
declare @NUMCARTAO integer
declare @NIF_ANTIGO varchar(20)
declare @IS_CENTRAL_LX as char(2)
select @IS_CENTRAL_LX = TP_CENTRAL from parametros
--Não actualiza no Central sincronizador
if ISNULL(@IS_CENTRAL_LX, ''-1'') <> ''LX''
begin
select @NIF_ANTIGO=NIF_ANTIGO from inserted
--Coloca o Status a Inactivo ''I'' de todos os cartões do Contribuinte ( porque apenas pode estar 1 Activo )
update CARTOES
set
STATUS = ''I'',
DT_ANULACAO = getdate()
where NIF_ANTIGO=@NIF_ANTIGO and STATUS = ''A''
--Obtem o Maior Número de Cartões
select @NUMCARTAO=IsNull( MAX(NUMCARTAO), 0 ) + 1
from CARTOES
where NIF_ANTIGO=@NIF_ANTIGO
INSERT INTO CARTOES ( NIF_ANTIGO, NIF, NOME, DT_EMISSAO, DT_VALIDADE, DT_ANULACAO, NUMCARTAO, STATUS, DESCR_REP_FISCAL, UTILIZADOR )
SELECT NIF_ANTIGO, NIF, NOME, DT_EMISSAO, DT_VALIDADE, DT_ANULACAO, @NUMCARTAO, ''A'', DESCR_REP_FISCAL, UTILIZADOR from inserted
end else
begin
INSERT INTO CARTOES ( COD_ID, NIF_ANTIGO, NIF, NOME, DT_EMISSAO, DT_VALIDADE, DT_ANULACAO, NUMCARTAO, STATUS, DESCR_REP_FISCAL, UTILIZADOR )
SELECT COD_ID, NIF_ANTIGO, NIF, NOME, DT_EMISSAO, DT_VALIDADE, DT_ANULACAO, @NUMCARTAO, STATUS , DESCR_REP_FISCAL, UTILIZADOR from inserted
end
end'
exec @var1
drop table CARTOESTEMP
END
When i run it, i receive the error:
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to 'CARTOES'.
Msg 203, Level 16, State 2, Procedure spSGCT_ALTERACARTOESTEMP, Line 65
The name 'CREATE TRIGGER TRG_INS_CARTOES ON dbo.CARTOES
INSTEAD OF INSERT
AS
begin
declare @NUMCARTAO integer
declare @NIF_ANTIGO varchar(20)
declare @IS_CENTRAL_LX as char(2)
select @IS_CENTRAL_LX = TP_CENTRAL from parametros
--Não actualiza no Central sincronizador
if ISNULL(@IS_CENTRAL_LX, '-1') <> 'LX'
begin
select @NIF_ANTIGO=NIF_ANTIGO from inserted
...
Can someone help, please?
Thank you
June 1, 2011 at 10:27 am
This command is responsible for the first 2 lines of the warning :
EXEC sp_rename 'CARTOESTEMP', 'CARTOES'
The rest of the error message is incomplete. My guess is that there's an error in the dynamic sql and it can't run.
I debug this by doing a print instead of exec. Then copy / paste and tweak untill it works. Then you know what to change in the dynamic sql.
Why do you need to run this on a regular basis?? I've never seen the need to use a proc for something like this.
June 1, 2011 at 10:45 am
The error was lack of ()
now, when i execute it i receive another error:
Msg 156, Level 15, State 1, Procedure TRG_INS_CARTOES, Line 32
Incorrect syntax near the keyword 'else'.
Msg 156, Level 15, State 1, Procedure TRG_INS_CARTOES, Line 37
Incorrect syntax near the keyword 'end'.
It seems like the "end else begin" expression is causing some kind of problem...
June 1, 2011 at 10:56 am
It must be something really simple.
Both the proc and the dynamic sql successfully compile on my PC and I just can't pick up the problem area just from reading the code.
Did you know that if you double-click on the error in QA it should take you directly to the line that caused the error (it's buggy sometimes but it's a start)
June 1, 2011 at 11:04 am
Yes, i cliked and it redirect me to the END ELSE BEGIN...
June 1, 2011 at 11:11 am
river1 (6/1/2011)
Yes, i cliked and it redirect me to the END ELSE BEGIN...
Ok then it's possibly the buggy part...
Try just running the printed version of the dynamic code.
Start by commenting the last 75% of the code and uncomment untill it fails.
BTW your trigger seems to handle only 1 line of data, is it the desired behavior?
June 1, 2011 at 11:14 am
When does the trigger fail? Creation time or execution time?
If it's at execution you could possibly have another trigger throwing that error tho I find this highly unlikely.
June 1, 2011 at 11:20 am
exec (@var1)
Need brackets or the contents of the variable are interpreted as an object name.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2011 at 11:22 am
GilaMonster (6/1/2011)
exec (@var1)Need brackets or the contents of the variable are interpreted as an object name.
He figured that 1 out in his 2nd post.
June 1, 2011 at 11:27 am
Missed that.
Ok, for the rest, replace the EXEC with Print (and remove the brackets) and post the printed out statement
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2011 at 11:29 am
if fails at creating
June 1, 2011 at 11:30 am
CREATE TRIGGER TRG_INS_CARTOES ON dbo.CARTOES
INSTEAD OF INSERT
AS
begin
declare @NUMCARTAO integer
declare @NIF_ANTIGO varchar(20)
declare @IS_CENTRAL_LX as char(2)
select @IS_CENTRAL_LX = TP_CENTRAL from parametros
--Não actualiza no Central sincronizador
if ISNULL(@IS_CENTRAL_LX, '-1') <> 'LX'
select @NIF_ANTIGO=NIF_ANTIGO from inserted
--Coloca o Status a Inactivo 'I' de todos os cartões do Contribuinte ( porque apenas pode estar 1 Activo )
update CARTOES
set
STATUS = 'I',
DT_ANULACAO = getdate()
where NIF_ANTIGO=@NIF_ANTIGO and STATUS = 'A'
--Obtem o Maior Número de Cartões
select @NUMCARTAO=IsNull( MAX(NUMCARTAO), 0 ) + 1
from CARTOES
where NIF_ANTIGO=@NIF_ANTIGO
INSERT INTO CARTOES ( NIF_ANTIGO, NIF, NOME, DT_EMISSAO, DT_VALIDADE, DT_ANULACAO, NUMCARTAO, STATUS, DESCR_REP_FISCAL, UTILIZADOR )
SELECT NIF_ANTIGO, NIF, NOME, DT_EMISSAO, DT_VALIDADE, DT_ANULACAO, @NUMCARTAO, 'A', DESCR_REP_FISCAL, UTILIZADOR from inserted
end else
begin
INSERT INTO CARTOES ( COD_ID, NIF_ANTIGO, NIF, NOME, DT_EMISSAO, DT_VALIDADE, DT_ANULACAO, NUMCARTAO, STATUS, DESCR_REP_FISCAL, UTILIZADOR )
SELECT COD_ID, NIF_ANTIGO, NIF, NOME, DT_EMISSAO, DT_VALIDADE, DT_ANULACAO, @NUMCARTAO, STATUS , DESCR_REP_FISCAL, UTILIZADOR from inserted
end
end
June 1, 2011 at 11:33 am
There's no begin after <> 'LX'
Reformatted code...
CREATE TRIGGER TRG_INS_CARTOES ON dbo.CARTOES
INSTEAD OF INSERT
AS
begin
declare @NUMCARTAO integer
declare @NIF_ANTIGO varchar(20)
declare @IS_CENTRAL_LX as char(2)
select
@IS_CENTRAL_LX = TP_CENTRAL
from
parametros
--Não actualiza no Central sincronizador
if ISNULL(@IS_CENTRAL_LX , '-1') <> 'LX'
begin
select
@NIF_ANTIGO = NIF_ANTIGO
from
inserted
--Coloca o Status a Inactivo 'I' de todos os cartões do Contribuinte ( porque apenas pode estar 1 Activo )
update
CARTOES
set
STATUS = 'I'
, DT_ANULACAO = getdate()
where
NIF_ANTIGO = @NIF_ANTIGO
and STATUS = 'A'
--Obtem o Maior Número de Cartões
select
@NUMCARTAO = IsNull(MAX(NUMCARTAO) , 0) + 1
from
CARTOES
where
NIF_ANTIGO = @NIF_ANTIGO
INSERT INTO
CARTOES
(
NIF_ANTIGO
, NIF
, NOME
, DT_EMISSAO
, DT_VALIDADE
, DT_ANULACAO
, NUMCARTAO
, STATUS
, DESCR_REP_FISCAL
, UTILIZADOR
)
SELECT
NIF_ANTIGO
, NIF
, NOME
, DT_EMISSAO
, DT_VALIDADE
, DT_ANULACAO
, @NUMCARTAO
, 'A'
, DESCR_REP_FISCAL
, UTILIZADOR
from
inserted
end
else
begin
INSERT INTO
CARTOES
(
COD_ID
, NIF_ANTIGO
, NIF
, NOME
, DT_EMISSAO
, DT_VALIDADE
, DT_ANULACAO
, NUMCARTAO
, STATUS
, DESCR_REP_FISCAL
, UTILIZADOR
)
SELECT
COD_ID
, NIF_ANTIGO
, NIF
, NOME
, DT_EMISSAO
, DT_VALIDADE
, DT_ANULACAO
, @NUMCARTAO
, STATUS
, DESCR_REP_FISCAL
, UTILIZADOR
from
inserted
end
end
June 1, 2011 at 11:34 am
BTW I still don't understand why you need to run this in a proc.
And your trigger still fails if more than 1 row is affected in the transaction that fires the trigger.
Is this the expected behavior?
June 1, 2011 at 11:35 am
GilaMonster (6/1/2011)
Missed that.Ok, for the rest, replace the EXEC with Print (and remove the brackets) and post the printed out statement
Looks like a fixed by posting a question effect.
I did exactly that and everything worked fine except for the part that I don't have those tables my system.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply