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

SQL Server Development (2000)

Trigger

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-26 : 12:17:11

There is a regular insertion / import of rows to a table every 5 minutes.

I create a stored procedure which is working perfectly - it simply updates a column in these new rows - Status = 'PENDING'. Also to change a flag in a database elsewhere, matching a code joining the columns. Runs manually no problem.

When I create a trigger to call this procedure when the data arrives, which does nothing at all than this:

CREATE TRIGGER PDA on <tablename>
AFTER INSERT
AS
exec workOrderStatusToPending
Go

The entire table is truncated and no more rows seen again until I drop the trigger. What could be wrong here?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-26 : 12:27:38

Why you use a SP here? cant you directly implement function inside trigger? like:-

CREATE TRIGGER PDA on <tablename>
AFTER INSERT
AS
UPDATE t
SET t.Status='PENDING'
FROM Table t
INNER JOIN INSERTED i
ON i.PK=t.PK

...do rest of operations
Go

PK is primary key

if you are in doubt, post full reqmnt and i will try to implement it within the triger

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-27 : 04:12:59

The strangeness is that the SP is fine when executed manually. When run as the body of the trigger, errors are generated. The contents of the SP are thus:

-- ADD PENDING to Analysis Code in ConstructMobile
update q
set q.ccs_quo_analysis_code = 'PENDING'
from mobile_solution_process msp
inner join "KEN-SQL-002".ConstructMobile.dbo.ccs_quotes q
on msp.work_order = q.ccs_quo_ref_number#2

-- FLAG+ Construct_Status in MSP
update msp
set msp.construct_status_flag = 1
from mobile_solution_process msp
inner join "KEN-SQL-002".ConstructMobile.dbo.ccs_quotes q
on msp.work_order = q.ccs_quo_ref_number#2

GO

There is another SP, which works fine, to populate the MSP table, and while run with this trigger in place, this error is generated:

MSP populated
Server: Msg 18452, Level 14, State 1, Procedure WorkOrderStatusToPENDING, Line 5

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-27 : 04:16:06

-- ADD PENDING to Analysis Code in ConstructMobile
update q
set q.ccs_quo_analysis_code = 'PENDING'
from mobile_solution_process msp
inner join [KEN-SQL-002].ConstructMobile.dbo.ccs_quotes q
on msp.work_order = q.[ccs_quo_ref_number#2]

-- FLAG+ Construct_Status in MSP
update msp
set msp.construct_status_flag = 1
from mobile_solution_process msp
inner join [KEN-SQL-002].ConstructMobile.dbo.ccs_quotes q
on msp.work_order = q.[ccs_quo_ref_number#2]

E 12°55'05.25"
N 56°04'39.16"

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-27 : 04:20:42

I will admit.

When saving this SP, it wasn't allowed due to the external database use, "ken-sql-002" generating an error

I created this SP using Query Analyzer, which worked. Maybe this is the problem? I'm not allowed to save this script in the trigger, either...

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-27 : 04:23:04

saving this SP? you mean creating sp in db?What was the error you got?

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-27 : 04:28:09

Peso

I saw your suggestion after my last post. Looks promising. All saves allowed

The littlest things = successful projects

Thanks

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-27 : 04:32:39

OK

This is the main error.

Does anyone recognise it??

MSP populated
Server: Msg 7391, Level 16, State 1, Procedure WorkOrderStatusToPENDING, Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Thanks

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-27 : 04:39:32

Current trigger is like this:

CREATE TRIGGER PDA ON [dbo].[mobile_solution_process]
AFTER INSERT
AS
print 'MSP populated'

-- ADD PENDING to Analysis Code in ConstructMobile
update q
set q.ccs_quo_analysis_code = 'PENDING'
from mobile_solution_process msp
inner join [KEN-SQL-002].ConstructMobile.dbo.ccs_quotes q
on msp.work_order = q.[ccs_quo_ref_number#2]

-- FLAG+ Construct_Status in MSP
update msp
set msp.construct_status_flag = 1
from mobile_solution_process msp
inner join [KEN-SQL-002].ConstructMobile.dbo.ccs_quotes q
on msp.work_order = q.[ccs_quo_ref_number#2]

Error when running the row insert job is:

Server: Msg 7391, Level 16, State 1, Procedure PDA, Line 7
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-27 : 04:42:09

When I change the trigger to run the SP (same contents as above), the error is this:

MSP populated
Server: Msg 7391, Level 16, State 1, Procedure WorkOrderStatusToPENDING, Line 5
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

mikebird
Aged Yak Warrior

529 Posts

Posted - 2008-02-27 : 12:00:08

Well, here is the script I have which works just fine - a simple update to this table and another one. It works just fine when I run it. I want to set up a trigger on this.

-- ADD PENDING to Analysis Code in ConstructMobile
update q
set q.ccs_quo_analysis_code = 'PENDING'
from mobile_solution_process msp
inner join [KEN-SQL-002].ConstructMobile.dbo.ccs_quotes q
on msp.work_order = q.[ccs_quo_ref_number#2]

-- FLAG+ Construct_Status in MSP
update msp
set msp.construct_status_flag = 1
from mobile_solution_process msp
inner join [KEN-SQL-002].ConstructMobile.dbo.ccs_quotes q
on msp.work_order = q.[ccs_quo_ref_number#2]
GO

I want to have an AFTER INSERT on the mobile_solution_process table here so that each time rows are added (each 5 mins) this UPDATE is done. Is it OK to add more than one row at a time, by the way? The table fills up constantly, but as soon as I add this trigger, when the next 5 min period comes the table is truncated and I never see any more rows until I drop the trigger. How can a trigger cause a truncate?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources