0

I was having old tridion 2009 trigger, this is the code:

CREATE TRIGGER My_TABLE ON [ITEMS]  
FOR INSERT  
AS  
    INSERT INTO My_TABLE(ACTION, PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID)  
        SELECT 'ADD', PUBLICATION_ID, ITEM_REFERENCE_ID, ITEM_TYPE, LAST_PUBLISHED_DATE, URL, SCHEMA_ID  
        FROM inserted

In tridion 2009 URL and SCHEMA_ID where part of ITEMS table, however when we upgrade to Tridion 2011 SP1, these columns do no longer exist in the ITEMS table.

SCHEMA_ID is moved to the COMPONENT table, I am little curious how to get the URL for each type of items (page, component etc. do we need to use link_info table to get url)

Just let me know which table would be used to get the URL as it was in ITEMS table before

Thanks.

Frank van Puffelen
  • 565,676
  • 79
  • 828
  • 807
Manoj Singh
  • 7,569
  • 34
  • 119
  • 198
  • 4
    This approach most likely voids your support warranty. The DB is black box and is off limits for writing data or creating any objects directly via SQL. You should be using either the Tridion Core Service or Tom.net APIs to interface with the Content Manager. – Nickoli Roussakov Dec 25 '12 at 20:54
  • You're right about the warranty @NickoliRoussakov. But in this case you'd need the event system, instead of (in combination with) the APIs you mention. ;-) – Frank van Puffelen Dec 26 '12 at 13:48

1 Answers1

3

There's a reason why Database details are never documented: SDL does not support access to that data at this level.

You have a pretty rich API to retrieve the data you need whenever you need it, and this allows SDL to constantly improve the database layer without having to worry about implementation support.

In your case, it might help if you explain a few things here...

  1. Which Database is this? Reading the column names it looks like it's a Content Delivery database?
  2. Why do you want to store the published URL? It's already there.

In case it's not clear yet, you should use the Linking classes to get the URL, not the LINK_INFO table. A properly setup environment will cache these values so you don't need to worry about any database impact. If this is not yet enough, and you really do need a custom table with custom values, then consider using a Deployer Extension that populates your table(s) as needed.

Nuno Linhares
  • 10,214
  • 1
  • 22
  • 42
  • thanks for reply. The above implementation we did to get latest published URLs and after doing the our Autonomy crawling webservice will update same table with Flag="Old", so that we next time we get the latest data . I know this implmentation is not supported and suggested by Tridion and this could cause support voilation. Please suggest whether same implementation can be achieved by using deployer extensions? If we use Deployer extension then we would store same datas into XML and it could cause some IO operations delay, please suggest right approach on this. – Manoj Singh Dec 27 '12 at 07:40
  • Which Database is this? Reading the column names it looks like it's a Content Delivery database? Yes it is Content Delivery Database Why do you want to store the published URL? It's already there. We want published URL because after some autonomy crawling our web service will update same table with "Flag = 'Old'" so that next time our webservice picks only latest published URLs. – Manoj Singh Dec 27 '12 at 07:40
  • 1
    Well, the supported way is to NOT use a database trigger, as SDL doesn't support changes to the database. It _might_ work, but it's still not supported, and as you noticed we change the database between versions. The supported way would be a Storage or Deployer Extension. Sample Deployer extension here: http://stackoverflow.com/a/11015253/866454 – Nuno Linhares Dec 28 '12 at 14:15