I’ve got some tables with the year is part of the name, for example: TABLE2006, TABLE2007, etc.. .The year of the name of table I will read in the table INSERTED of my Trigger : I nead to create a trigger where I update those tables :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [TESTE]
ON[dbo].[TABTESTE]
FOR INSERT
AS
DECLARE
@.YearTablenvarchar(4),
@.IdClientINT,
@.MyTableTABLE
(
IdClientINT,
SituNVARCHAR(50)
)
BEGIN
SET NOCOUNT ON;
SELECT @.YearTable = SITUACAO, @.IdClient = IdClient FROM INSERTED
SET @.MyTable = 'TABLE' & @.YearTable
UPDATE@.MyTable
SET
Situ= 'X'
WHEREIdClient = @.IdClient
END
GO
Erros:
Msg 156, Level 15, State 1, Procedure TESTE, Line 9
Incorrect syntax near the keyword 'TABLE'.
Msg 137, Level 15, State 1, Procedure TESTE, Line 17
Must declare the scalar variable "@.MyTable".
Msg 1087, Level 15, State 2, Procedure TESTE, Line 18
Must declare the table variable "@.MyTable".
I don't have much experience working with triggers but here's a shot...
-Try changing the @.MyTable to varchar(100)
-Declare another variable @.sql varchar(500)
set @.sql = 'update ' + @.MyTable + ' set situ = '''X''' where IdClient = ' + @.IdClient
exec (@.sql)
You may need to play with the number of single quotes around X to get the string to build correctly.
|||can you explain what are you trying to do with this statement.
SET @.MyTable = 'TABLE' & @.YearTable
Are you trying to concatenate the string?
|||It seems to me that he thinks that TABLE variables are some kind of references/pointers or interfaces, and that he can use TABLE var with the existing table ('TABLE' & @.YearTable) of the identical structure as TABLE var.I guess that he, in fact, wants to create and execute some dynamic SQL string based on the inserted values.|||TABLE var is a table just like any "normal" table, it resides in the memory or in tempdb. It si ont some kind of reference!
You can't set it to a string, just like you can't do that with any "normal" table.
Are you trying to update the appropritate table ('TABLE' & @.YearTable) depending on the inserted value (@.YearTable = SITUACAO ... FROM INSERTED)?
|||
hi MauricioBogo,
declare @.MyTable Table
(
IdClient int,
Suit nvarchar(50)
)
after you declare @.MyTable as a "Table"
in this Transaction, the @.MyTable is a "local database object" Already.
not a variable value.
this is why you can't do this → " SET @.MyTable = 'TABLE' + @.Yeartable "
and update @.MyTable .
you can try this, as below:
declare @.sSQL varchar(Max)
declare @.Mytable varchar(255)
select @.yeartable = SITUACAO , @.IDClient = IDClient From Inserted
set @.MyTable = 'TABLE' + @.Yeartable
set @.sSQL = 'update ' + @.MyTable + ' set Situ ='X'
exec (@.sSQL)
--or--
or add any script you need.
try it.
hoping this can help.
Best Regrads,
Hunt.
|||Yes, I'm trying to concatenate the string, but I already changed to + . Right ?
Thanks
|||Hunt
Ok it works, thanks
Mauricio
|||Table variables need to be declare alone:
DECLARE
@.YearTable nvarchar(4),
@.IdClient INT
DECLARE
@.MyTable TABLE
(
IdClient INT,
Situ NVARCHAR(50)
)
sql
