April 11, 2012 at 8:54 am
I have a trigger that gets executed when a serena SBM workflow item is created. Serena has no help for me on this that's why i'm asking for sql server help.
I am using the trigger to keep the newly created items in sync. There are 2 types of new items brand new and revisions. The brand new item in the trigger works the revision doesn't.
On the revision the trigger calls a procedure in the other database to create the main record from the existing one than transfer the related items to new records that will be related to the new revision. It stops working at the pint where I am transferring the related items. I commented out sections until I figured out where it stopped working. On its own the procedure works fine. It is only when executed through the trigger that the procedure fails. Any ideas why? It can potentially be alot of data transferring but I chose a small base item to revision. At the pint the procedure fails I begin to use temporary tables to transfer the data. Not sure, could that be causing problems?
Its hard to know what is going on in a trigger. Can someone point me to some trigger error checking articles?
April 11, 2012 at 8:59 am
Can you post the code? It will be a lot easier to provide some help if we can see the code or at least a reasonable facsimile of it.
What does not working mean? Are you getting an error message or are you just not seeing expected data transfer?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 11, 2012 at 9:00 am
Pretty sparse on details.
It is only when executed through the trigger that the procedure fails.
Do you have error messages?
Can you post the trigger code?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2012 at 9:01 am
Looks like Jack was posting pretty much the same response at the same time as me. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2012 at 9:02 am
paste the CREATE TRIGGER definition here, and the CREATE TABLE definitions for the two tables in question;
with that, we should be able to show you exactly what the issue is.
also, the exact error you get will reeally be very important as well...paste that here too!
Lowell
April 11, 2012 at 9:02 am
=echo=
i'm just Jack and Sean's Parrot today!
Lowell
April 11, 2012 at 11:21 am
the exact error is "The transition "Submit" was not completed Error attempting to add to database.
Database error: this operation could not be completed possible because the database is read-only.(TTexcIDS_EXC_DB_IS_READ_ONLY)Update or delete failed. " This appears in the Serena SBM interface. No database that it is dealing with is read-only this was confirmed by my DBA.
By not working I mean the insert and everything after it doesn't happen.
I would love to post the code but it exists on another network and I'm not permitted to transfer.
The trigger code in DB A simply gathers the 2 ids needed (the last record ID and the new revision id) from the inserted table then calls a procedure in DB B.
The procedure inserts a record then gets the ID of that record and copies all the records related to the original to the new one.
I'll post some kind of better example tomorrow. But if you come up with any ideas before then please post them.
Thanks
April 11, 2012 at 11:24 am
Looks to me that the problem is not in the trigger but at the other end.
April 11, 2012 at 2:43 pm
Can you create a workflow item using SSMS instead of the UI? I don't think you are getting an accurate error message back from the UI.
It sounds to me like a permissions issue. You are going across databases and by default cross database ownership chaining is off. Essentially that will leave you with 2 options (not necessarily in order of preference):
1. Grant the login that is creating the workflow item from the UI EXECUTE permissions on the stored procedure in the second database.
2. Sign the trigger and create a user in the second database that has EXECUTE permissions on the stored procedure.
Here's a blog post about it, http://wiseman-wiseguy.blogspot.com/2009/11/maintaining-security-and-performance.html
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 16, 2012 at 1:16 pm
Sorry got hit with a sinus infection and couldn't work.
We tried the couple options given on execute permissions and I had my DBA read the link Jack sent. That didn't fix it so here is a code snippet.
Please assume that all variables that I don't bother declaring are declared in the real code. It might be something simple that is wrong but its not that simple or the code wouldn't execute when called form somewhere other than the trigger. This is for transferring connected data from 1 revision to the next.
I update tables in both databases. All updates prior to this section execute. From here on I don't think anything happens when executed from the trigger in the other database.
All this code works when not executed from the trigger.
--create temp tables to help match the old data with the new
Declare @new Table (Match int identity
, ID_Itemtable int
,MainTableID int
, otherdata int)
Declare @Old Table (Match int identity
, ID_Itemtable int
,MainTableID int
, otherdata int)
Insert Into <DB>.dbo.Itemtable
(MainTableID,
, otherdata
)Output inserted.ID_Itemtable , inserted.MainTableID, inserted.otherdata into @NewFacts (ID_Itemtable,MainTableID, otherdata)
Select @NewMainTableID
, otherdata
from ItemTable
Where MainTableID = @MT
order by otherdata
Insert into @Old Match(
ID_Itemtable
,MainTableID
, otherdata )
Select
ID_Itemtable
,MainTableID
, otherdata
from ItemTable
Where MainTableID = @MT
order by otherdata
then I transfer the data into about 10 3rd layer tables like this
Insert into tables3
(ID_Itemtable
, table3_data
)
Select i.ID_Itemtable
, table3_data
From tables3 t
join @Old o
on o.ID_Itemtable = t.ID_Itemtable
join @new i
on i.Match = o.Match
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply