Problem in Creating Trigger Sql Server 2000

Friends,
I have problem to creating a trigger.
In a trigger, i have check, if both inserted and deleted table has
row, then Check containt in both tables are same or not like this
Select Count(*) from inserted i , deleted d where i.cola = d.cola
and i.colb = d.colb
I have done it in some dynamically way, which can work for my all
tables, As code follows.
########
My Problem is, when following trigger execute, its return inserted
and deleted table does not exits.
#######
Update tm_empmastR1
Set empstatus = 'A'
Alter Trigger tr_tm_empmastR1 on tm_empmastR1
For Insert, Update, Delete
As
Declare @.ColName as VarChar(255),
@.Cmd as VarChar(8000)
Declare TCur Cursor For
Select SC.name
From sysobjects SO
Inner join syscolumns SC On SC.id = SO.id
And SO.name = 'tm_empmastR1'
order by colid
Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
Open TCur
Fetch Next From TCur Into @.ColName
While (@.@.Fetch_Status = 0)
Begin
Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
Fetch Next From TCur Into @.ColName
End
Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
Print (@.Cmd)
Exec (@.Cmd)
Close TCur
Deallocate TCur
#####
Although return Sql is runing perfectly.
#####
Thanks in advance for any reply.
Rahul Verma
FutureSoftDynamically executed SQL has its own context and doesn't have access to these tables. I encourage
you to skip the dynamic SQL if at all possible. If not, you can have the outer code populate a temp
table and then the inner (dynamic) code can access that temp table.
I would use the system tables/catalog views to generate the trigger code, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rahul" <verma.career@.gmail.com> wrote in message
news:1172035114.793535.273980@.v33g2000cwv.googlegroups.com...
> Friends,
> I have problem to creating a trigger.
> In a trigger, i have check, if both inserted and deleted table has
> row, then Check containt in both tables are same or not like this
> Select Count(*) from inserted i , deleted d where i.cola = d.cola
> and i.colb = d.colb
> I have done it in some dynamically way, which can work for my all
> tables, As code follows.
> ########
> My Problem is, when following trigger execute, its return inserted
> and deleted table does not exits.
> #######
>
> Update tm_empmastR1
> Set empstatus = 'A'
> Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> For Insert, Update, Delete
> As
> Declare @.ColName as VarChar(255),
> @.Cmd as VarChar(8000)
> Declare TCur Cursor For
> Select SC.name
> From sysobjects SO
> Inner join syscolumns SC On SC.id = SO.id
> And SO.name = 'tm_empmastR1'
> order by colid
> Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> Open TCur
> Fetch Next From TCur Into @.ColName
> While (@.@.Fetch_Status = 0)
> Begin
> Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> Fetch Next From TCur Into @.ColName
> End
> Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> Print (@.Cmd)
> Exec (@.Cmd)
> Close TCur
> Deallocate TCur
>
> #####
> Although return Sql is runing perfectly.
> #####
>
> Thanks in advance for any reply.
> Rahul Verma
> FutureSoft
>|||Hi Rahul
"Rahul" wrote:
> Friends,
> I have problem to creating a trigger.
> In a trigger, i have check, if both inserted and deleted table has
> row, then Check containt in both tables are same or not like this
> Select Count(*) from inserted i , deleted d where i.cola = d.cola
> and i.colb = d.colb
> I have done it in some dynamically way, which can work for my all
> tables, As code follows.
> ########
> My Problem is, when following trigger execute, its return inserted
> and deleted table does not exits.
> #######
>
> Update tm_empmastR1
> Set empstatus = 'A'
> Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> For Insert, Update, Delete
> As
> Declare @.ColName as VarChar(255),
> @.Cmd as VarChar(8000)
> Declare TCur Cursor For
> Select SC.name
> From sysobjects SO
> Inner join syscolumns SC On SC.id = SO.id
> And SO.name = 'tm_empmastR1'
> order by colid
> Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> Open TCur
> Fetch Next From TCur Into @.ColName
> While (@.@.Fetch_Status = 0)
> Begin
> Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> Fetch Next From TCur Into @.ColName
> End
> Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> Print (@.Cmd)
> Exec (@.Cmd)
> Close TCur
> Deallocate TCur
>
> #####
> Although return Sql is runing perfectly.
> #####
>
> Thanks in advance for any reply.
> Rahul Verma
> FutureSoft
>
The dynamic SQL is in a different scope to the trigger, therefore the it
doesn't know about the inserted and deleted tables.
You should check @.@.ROWCOUNT as the first statement in the trigger to make
sure that there has been some changes.
All the cursor is doing is allowing you to avoid having to change the
trigger when your tables structure changes, on a mature system it will be
redundant and could potentially impact performance and could even cause a
bottleneck.
John|||On Feb 21, 1:17 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Rahul
>
>
> "Rahul" wrote:
> > Friends,
> > I have problem to creating a trigger.
> > In a trigger, i have check, if both inserted and deleted table has
> > row, then Check containt in both tables are same or not like this
> > Select Count(*) from inserted i , deleted d where i.cola = d.cola
> > and i.colb = d.colb
> > I have done it in some dynamically way, which can work for my all
> > tables, As code follows.
> > ########
> > My Problem is, when following trigger execute, its return inserted
> > and deleted table does not exits.
> > #######
> > Update tm_empmastR1
> > Set empstatus = 'A'
> > Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> > For Insert, Update, Delete
> > As
> > Declare @.ColName as VarChar(255),
> > @.Cmd as VarChar(8000)
> > Declare TCur Cursor For
> > Select SC.name
> > From sysobjects SO
> > Inner join syscolumns SC On SC.id = SO.id
> > And SO.name = 'tm_empmastR1'
> > order by colid
> > Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> > Open TCur
> > Fetch Next From TCur Into @.ColName
> > While (@.@.Fetch_Status = 0)
> > Begin
> > Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> > Fetch Next From TCur Into @.ColName
> > End
> > Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> > Print (@.Cmd)
> > Exec (@.Cmd)
> > Close TCur
> > Deallocate TCur
> > #####
> > Although return Sql is runing perfectly.
> > #####
> > Thanks in advance for any reply.
> > Rahul Verma
> > FutureSoft
> The dynamic SQL is in a different scope to the trigger, therefore the it
> doesn't know about the inserted and deleted tables.
> You should check @.@.ROWCOUNT as the first statement in the trigger to make
> sure that there has been some changes.
> All the cursor is doing is allowing you to avoid having to change the
> trigger when your tables structure changes, on a mature system it will be
> redundant and could potentially impact performance and could even cause a
> bottleneck.
> John- Hide quoted text -
> - Show quoted text -
Hi All
Thanks for reply.
Tibor Karaszi
i am not getting your idea,
trigger is called when we call update/insert/delete command
automatically and just after these statements.
So, we have not enough time to create and use a temp table.
If is it possible or any other idea, please mail me, Because we
require to create this type of modification with 100'th triggers, As i
am creating log.
Jhon,
This is right, if table structure change, cursor may be fail, that's
why i am using sysobject and syscolumns tables to getting all columns.
And when i use print statement, the return value of print statement
is absolutely correct and its working.
The @.@.rowcount only return no of rows in last query, its ok but
before this statement, i have to match all these fields in both table.
####
Actually my logic is
if both inserted and deleted tables contain row (ie row is updated)
and changes are actually made (ie changes must be done in any field in
a table) then insert this row in a log file with a particular flag.
else if only inserted table contain row then it insert with different
flag
same as with deleted.
####
if you have any idea to done it, please reply me
i have more than 1000 tables.
Thanks
Rahul|||Hi Rahul
"Rahul" wrote:
> On Feb 21, 1:17 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi Rahul
> >
> >
> >
> >
> >
> > "Rahul" wrote:
> > > Friends,
> > > I have problem to creating a trigger.
> > > In a trigger, i have check, if both inserted and deleted table has
> > > row, then Check containt in both tables are same or not like this
> >
> > > Select Count(*) from inserted i , deleted d where i.cola = d.cola
> > > and i.colb = d.colb
> >
> > > I have done it in some dynamically way, which can work for my all
> > > tables, As code follows.
> >
> > > ########
> > > My Problem is, when following trigger execute, its return inserted
> > > and deleted table does not exits.
> > > #######
> >
> > > Update tm_empmastR1
> > > Set empstatus = 'A'
> >
> > > Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> > > For Insert, Update, Delete
> > > As
> >
> > > Declare @.ColName as VarChar(255),
> > > @.Cmd as VarChar(8000)
> >
> > > Declare TCur Cursor For
> > > Select SC.name
> > > From sysobjects SO
> > > Inner join syscolumns SC On SC.id = SO.id
> > > And SO.name = 'tm_empmastR1'
> > > order by colid
> >
> > > Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> > > Open TCur
> > > Fetch Next From TCur Into @.ColName
> > > While (@.@.Fetch_Status = 0)
> > > Begin
> > > Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> > > Fetch Next From TCur Into @.ColName
> > > End
> >
> > > Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> > > Print (@.Cmd)
> > > Exec (@.Cmd)
> >
> > > Close TCur
> > > Deallocate TCur
> >
> > > #####
> > > Although return Sql is runing perfectly.
> > > #####
> >
> > > Thanks in advance for any reply.
> >
> > > Rahul Verma
> > > FutureSoft
> >
> > The dynamic SQL is in a different scope to the trigger, therefore the it
> > doesn't know about the inserted and deleted tables.
> >
> > You should check @.@.ROWCOUNT as the first statement in the trigger to make
> > sure that there has been some changes.
> >
> > All the cursor is doing is allowing you to avoid having to change the
> > trigger when your tables structure changes, on a mature system it will be
> > redundant and could potentially impact performance and could even cause a
> > bottleneck.
> >
> > John- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi All
> Thanks for reply.
> Tibor Karaszi
> i am not getting your idea,
> trigger is called when we call update/insert/delete command
> automatically and just after these statements.
> So, we have not enough time to create and use a temp table.
> If is it possible or any other idea, please mail me, Because we
> require to create this type of modification with 100'th triggers, As i
> am creating log.
>
> Jhon,
> This is right, if table structure change, cursor may be fail, that's
> why i am using sysobject and syscolumns tables to getting all columns.
> And when i use print statement, the return value of print statement
> is absolutely correct and its working.
> The @.@.rowcount only return no of rows in last query, its ok but
> before this statement, i have to match all these fields in both table.
> ####
> Actually my logic is
> if both inserted and deleted tables contain row (ie row is updated)
> and changes are actually made (ie changes must be done in any field in
> a table) then insert this row in a log file with a particular flag.
> else if only inserted table contain row then it insert with different
> flag
> same as with deleted.
> ####
> if you have any idea to done it, please reply me
> i have more than 1000 tables.
> Thanks
> Rahul
I think you have misunderstood both replies. I believe that Tibor is
suggesting that you generate your create trigger statements using the cursors
and you will end up with "hardcoded" columns for each table.
The checking of @.@.ROWCOUNT at the very begining of the trigger will mean
that you don't execute the code when not rows are changed, this will cut out
the unnecessary actions of the trigger.
Alter Trigger tr_tm_empmastR1 on tm_empmastR1 For Insert, Update, Delete
As
IF @.@.ROWCOUNT = 0 RETURN
...
John