Error in trigger "Attempt to intitiate ...with results pending"?!

[marcarls]

Hi,
I have an urgent problem. I have created insert, update, and delete triggers for a table. They are pretty large...When i try to insert i new record, or change an exising...or do anything I recieve the message" Attempt to initiate a new SQL Server operation with results pending".
I suppose I have missed something. Can anyone help me please?
Regards Maria

 

[JamesLean]

Where are you trying to do these modifications from? Maybe this article will help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;165951

--James

 

[marcarls]

Thanks, I will read it and see if I understand anything ;-) My trigger is for audit functionality, when records are inserted/updated/deleted the change should be logged into another table (per column...thats why it is large...) It look something like this...(this is only for the first column, in reality it is 75 columns and there is a part for every column!)

CREATE TRIGGER tr_audit_i_actor ON [dbo].[ACTOR]
FOR INSERT
AS
BEGIN
/***********************************************
Column [ACTOR_ID]
************************************************/
BEGIN
SELECT @log_change = log_changes, @column_id = column_id, @table_id = table_id
FROM audit_pat_column WHERE column_db_name = 'ACTOR_ID'

IF @log_change = 1

INSERT INTO audit_pat_log (log_id, key_1, key_2, key_3, key_4, key_5, type_of_key, no_of_keys, table_name, table_id, column_name, column_id, action_type, old_value, new_value, edit_date, edit_by, host)
SELECT @log_id+1, convert(varchar(200), new.actor_id), null, null, null, null, 2, @no_of_keys, 'ACTOR', @table_id, 'ACTOR_ID', @column_id, 1, null, convert(varchar(200), new.actor_id), getdate(), suser_sname(), host_name()
FROM inserted new WHERE new.actor_id IS NOT NULL
IF @@rowcount > 0 Set @Inserted = 1
IF @inserted = 1
Set @log_id = @log_id + 1
Set @inserted = 0
END


/************************************************
Column[NAME1]
*************************************************/

Regards Maria

 

[marcarls]

Hi again,
I think I understand by the article that I do not take care of errors that might occur in my code. Do you have any suggestions on how I might write the code to check if something got wrong, to avoid pending result. In the article they gave a C++ example, but I am writing the code in the trigger using TSql. I you understand this is new to me I appreciate any help.

Regards Maria