Syntax error in trigger when try to call stored proc

[LucieLastic]

hi

Is it possible to call a stored proc from a Trigger? If so, I get a 'syntax error near keyword proc' and don't know why:

CREATE Trigger TempTrig
ON TempDil
FOR INSERT
AS
DECLARE @rows int --create variable to hold @@ROWCOUNT
select @rows=@@rowcount

if @rows = 0 --no rows inserted, so exit trigger
RETURN

EXEC proc spUpdateHistory(inserted.ID, inserted.Date, inserted.Factor)

-- Need to CATCH errors here
RETURN

Grateful for any help
lou

 

[JeanNiBee]

Take out the 'proc'.

Just use exec "procedure name" "parameters"

You must be using the Microsoft version of T-SQL syntax.

If you are using Sybase, lose the MS specific books. Not that they aren't a good place to start, but why confuse and frustrate yourself, get the online Sybase PDF and refer to that. It's chock full of great info.

Then you can use the Performance and Tuning guides.

Or, check out Rob Verschoor's site for two GREAT books

http://www.sypron.nl

-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[JayKusch]

Even SQL Server does not use the "proc" in the execution syntax of an SP.

Thanks

J. Kusch

 

[LucieLastic]

hi

The 'proc' bit has gone. The main problem is I have to do a select on the inserted table. The trigger now works but I'm at home now (don't have it here) and will post the working 'code' on Monday.

Thanks for the replies and the suggestions on books
lou

 

[LucieLastic]

hi

JeanNiBee: I've ordered those 2 books, thanks for the suggestion.

Here's my trigger as it stands at the moment:

CREATE Trigger TempTrig
ON TempDil
FOR INSERT
AS
DECLARE @tempID int
DECLARE @tempDate DateTime
DECLARE @tempFactor float

if @@rowcount = 0
RETURN

SELECT @TempID = ID , @TempDate = Date, @TempFactor = Factor
FROM inserted

EXEC spUpdateData @ID = @TempID , @Date = @TempDate, @Factor = @TempFactor

RETURN

Another quick question: I create a temp table (using #) in my Stored proc (called from Trigger above) but get the error "The CREATE TABLE command is not allowed within a multi-statement transaction in the 'tempdb' database". This stored procedure works exactly right when called directly from isql but get the error when called from the trigger. Do I need to create the temp table in the trigger before calling the proc? - I'll try that, otherwise I'll create permanent table in the database.

many thanks
lou

 

[LucieLastic]

I've found out a temp table can not be (safely) created from a trigger, so I'll create a permanent table. This is what I found in the Sybase online:-

Error 2762 can be raised when a command creates or drops temporary objects (objects in tempdb) within the context of a multi-statement transaction. For example, this code may generate a 2762 error:

1> begin transaction
2> create table #cities 
3> (city_name  char(15)  not null)
4> commit transaction
5> go

The error is raised when ddl in tran is set to FALSE in tempdb. Use one of the following strategies to correct this error:


Use a permanent object name, so that tempdb is not affected.

Execute the command outside a multi-statement transaction.

Warning!
Using data definition language commands on tempdb within transactions may cause concurrency problems in tempdb. Always leave ddl in tran set to FALSE in tempdb.

 

[JeanNiBee]

That is correct you can't create a temp table (or assume it's safe to create one) from a Trigger.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[LucieLastic]

hi JeanNiBee

Just a quicky, received the books and they're great. From inital quick 'flip throughs', I really like the quick reference as it has everything - I haven't had much chance to have a good look at the Tips & Tricks book yet but initial impression is good, looking at the index.

many thanks again for the suggestion
lou

 

[JeanNiBee]

Verschoor is a GREAT author.

My purple reference is so bent and used as EVERYONE in the office loves it for quick checks.

Glad to have been able to help.



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects