Triggers are not firing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Triggers are not firing

Author

Topic

Progress2007
Starting Member

17 Posts

Posted - 2009-07-28 : 10:33:41

Hi
I have a Table named as Table 1 . i have created a Trigger for Insert
in this table.

my problem is that when i using this statment my trigger is not firing

Insert Into Table1(Col1,Col2,Col3)
Select Col1,Col2,Col3 from Table2

Table1 and Table 2 have same data.......

Please tell me how can i do this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-28 : 10:34:20

show us your trigger code

KH
[spoiler]Time is always against us[/spoiler]

Progress2007
Starting Member

17 Posts

Posted - 2009-07-29 : 07:03:15

Ok sir heew is the code
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER trigger [updateTableTime] on [dbo].[TableTime]
for insert
as
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @TrkIndexNumber int;
DECLARE @CategoryCode nvarchar(50);
DECLARE @UserId varchar(50);
DECLARE @PID int;
DECLARE @insertedUserID nvarchar(25);

DECLARE @sKCode1 nvarchar(25);
DECLARE @TableName1 varchar(500);

select @PID = TrkIndexNumber,
@sKCode1 = KCode1,
from inserted;

-- Start For KCODE1
set @TableName1 = (select LinkTable from TrkTableLink where LinkType = 'Category' and LinkOrder=1)

SET @SQLString = N'SELECT @TrkIndexNumberOut = TrkIndexNumber
FROM ' + @TableName1 + '
WHERE CategoryCode = @CatCode';

SET @ParmDefinition = N'@CatCode nvarchar(25),
@TrkIndexNumberOut int OUTPUT';

-- Checking whether the value in @sKcode1 is Null or not.
if (len(@sKCode1) = 0 or (@sKCode1 is null))
set @sKCode1 = ''

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@CatCode = @sKCode1,
@TrkIndexNumberOut = @TrkIndexNumber OUTPUT;

-- This SELECT statement returns the value of the OUTPUT parameter.
if len(@sKCode1) > 0
begin
update TableTime
set KINum1 = @TrkIndexNumber where TrkIndexNumber = @PID;
end

Andre412
Starting Member

11 Posts

Posted - 2009-07-29 : 08:02:24

you are performing essentially a bulk insert

either specify to fire triggers on the insert transaction or design your trigger to handle multiple records in the inserted table

me http://drayblog.gotdns.com
company http://www.lowcarboneconomy.com

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-29 : 09:31:43

sooooooooooooooooooooooooooo many bad things here

First off dynamic sql assigning a variable will be gobne as soon as the execution is over, so that's useless

Second, Dynamic SQL is just not a good idea for system architecture.

Third, Keep triggers simple..remeber a trigger fires EVERY TIME the action occurs. If you know when the actions are suppose to happen just use a sproc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-29 : 11:32:16

quote:
Originally posted by X002548

sooooooooooooooooooooooooooo many bad things here

First off dynamic sql assigning a variable will be gobne as soon as the execution is over, so that's useless

Second, Dynamic SQL is just not a good idea for system architecture.

Third, Keep triggers simple..remeber a trigger fires EVERY TIME the action occurs. If you know when the actions are suppose to happen just use a sproc

Brett

Erm. I agree with you that dynamic calls in triggers are generally (read almost always and I've yet to see a good reason) a bad bad plan. And I agree with you about your Third point -- keep triggers as simple as possible,

Your First point is plain wrong though. he's using sp_executeSql and passing in a variable declared with the OUTPUT parameter. the value will persist after the dynamic call.

Your second point, I think might go to far. For the vast majority of dynamic sql posted here there is usually a better way but dynamic sql can sometimes still be the most appropriate and best performing method to use. However, as you said "System Architecture" you mean the way the database logic works rather than stored proc reports? (I can't really get a handle on a good term - I hope I'm making a valid distinction) then you are probably 100% correct.

I'm not trying to rain on your parade, I just think you are being too absolute.

progress2007: I know you posted your code but what are you actually trying to do?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-29 : 11:40:13

Progress2007:

you are assigning values to variables like this


select @PID = TrkIndexNumber,
@sKCode1 = KCode1, 
from inserted;

I take it then that there will only ever be 1 value at a time inserted into the table? If not then what will actually happen is that for ever row in inserted that column value will be assigned to the variable one after another and you will end up the value in the last row (ordered in no guaranteed way)

Is this what you wanted?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Progress2007
Starting Member

17 Posts

Posted - 2009-07-29 : 12:10:46

Thanks Charlie for your response.

As you mentioned that the last wor will be updated, the same thing is going on here and i don't want that.
Basicaly i am using a Query something like this.

Insert Into TableTime(Col1,Col2,Col3)
Select Col1, Col2,Col3 from TableTime2.

TableTime1 and TableTime2 has the same schema.
What i want that suppose there are 2000 rows then my trigger should be fired for 2000 times and

select @PID = TrkIndexNumber,
@sKCode1 = KCode1,
from inserted;

this should be updated for each row.....

Please suggest me , cause i m seeing some hope in your response

Progress2007
Starting Member

17 Posts

Posted - 2009-07-29 : 12:12:10

Thanks Charlie for your response.

As you mentioned that the last row will be updated, the same thing is going on here and i don't want that.
Basicaly i am using a Query something like this.

Insert Into TableTime(Col1,Col2,Col3)
Select Col1, Col2,Col3 from TableTime2.

TableTime1 and TableTime2 has the same schema.
What i want that suppose there are 2000 rows then my trigger should be fired for 2000 times and

select @PID = TrkIndexNumber,
@sKCode1 = KCode1,
from inserted;

this should be updated for each row.....

Please suggest me , cause i m seeing some hope in your response

quote:
Originally posted by Transact Charlie

Progress2007:

you are assigning values to variables like this

select @PID = TrkIndexNumber,
@sKCode1 = KCode1,
from inserted;

I take it then that there will only ever be 1 value at a time inserted into the table? If not then what will actually happen is that for ever row in inserted that column value will be assigned to the variable one after another and you will end up the value in the last row (ordered in no guaranteed way)

Is this what you wanted?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-29 : 19:16:26

quote:
Originally posted by Progress2007
Insert Into TableTime(Col1,Col2,Col3)
Select Col1, Col2,Col3 from TableTime2.

TableTime1 and TableTime2 has the same schema.
What i want that suppose there are 2000 rows then my trigger should be fired for 2000 times and
....

Absolutely NOT! -- if you insert 2000 rows then you want the trigger to fire exactly ONCE. Not 2000 times! (the trigger will only fire once regardless)

Forget what you have coded so far. It doesn't look good at all. You want your trigger to be quick and light on reads on writes.

What are you actually trying to do?

Can you explain with sample data and required results?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources