Weird Trigger behaviour - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Weird Trigger behaviour

Author

Topic

OMB
Yak Posting Veteran

88 Posts

Posted - 2004-03-10 : 08:58:07

Hi all

I have a trigger on a table that basically stores some SP text and additional information in fields in another table. All works fine except for every correct entry it also adds a null record.

Any ideas?

heres the trigger in question



Alter  TRIGGER ttr on [TH]
FOR UPDATE,INSERT
AS BEGIN 

 IF update (BH)

BEGIN 

DECLARE  @SQL VARCHAR (100)

select @SQL = 'spX_T @TID='''+ convert(varchar(20),i.tid) + ''''
from inserted i

		INSERT Td..MQueue(DatabaseName, DateCalled, StoredProcedure) VALUES(DB_NAME(),GETDATE(),@SQL)      
	
END

END

Cheers

OMB

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-10 : 11:03:59

Well... at first glance, you have to be thinking about sets...

inserted may have many rows...a trigger fires for a set of values...

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-10 : 11:56:40

try

IF update (BH)
BEGIN
INSERT Td..MQueue(DatabaseName, DateCalled, StoredProcedure)
select DB_NAME(),GETDATE(), 'spX_T @TID='''+ convert(varchar(20),i.tid) + ''''
from inserted i
END

But I don't see why your trigger inserts two rows.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

OMB
Yak Posting Veteran

88 Posts

Posted - 2004-03-11 : 03:23:52

Thanks Nigel

it worked, but why my method inserted a null row for every right one is still a mystery.

OMB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources