trigger problem - 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)

trigger problem

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-04-16 : 06:19:29

Hi pals,

Can we call a stored procedure within a Trigger Body.
Why because i am trying to populate the FTS catalog as n
when DML operation is performed on a table called "ITEM".

ALTER trigger PopulateFTS on ITEM FOR INSERT,UPDATE,DELETE
as
begin
exec sp_fulltext_catalog 'ItemKeywordsCatalog', 'start_full'
end;

Error :

Server: Msg 15002, Level 16, State 1, Procedure sp_fulltext_catalog, Line 36
The procedure 'sp_fulltext_catalog' cannot be executed within a transaction.

Any Suggestions....

Thanks in Advance,
Franky

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 06:21:55

The error message is fairly obvious, isn't it?
Put the call to the SP where the DML statement is executed.

Peter Larsson
Helsingborg, Sweden

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-04-16 : 06:50:53

That's fine. But assume that i am directly connecting Query Analyzer and then manually i am typing the DML stmt. Then how can i dot it.

I need a trigger.But my primary doubt is can we execute a stored procedure inside a trigger.

Regards,
Franky

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 06:52:59

In QA?

<DML HERE>
go
exec sp_fulltext_catalog 'ItemKeywordsCatalog', 'start_full'

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-16 : 07:00:12

1. It is possible to execute stored procedure inside trigger, not in this case though due to said limitation

2. Make those insert/update/delete centralized by using stored procedures to avoid user directing manipulating data using DML statements in QA

3. You can schedule to rebuild fulltext catalog at regular intervals rather than doing it on every DML statement execution.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources