Post reply

How to determine the table name within a trigger

  • February 15, 2005 at 6:13 am

    #65651

    I am designing a database where there the data will be held on separate monthly tables, such as "data200501", "data200502" etc... All of these tables will have INSTEAD OF INSERT, UPDATE and DELETE triggers to log the changes made by users. There won't be a separate change-log table for each data table, but instead a single table with a "month" column in the primary key.

    What I want to do is to determine the name of the table that the trigger belongs to, so that I can log the changes with period information.

    Any suggestions?

  • February 15, 2005 at 6:34 am

    #542256

    See if this helps http://www.umachandar.com/technical/SQL6x70Scripts/Main47.htm 

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 15, 2005 at 6:36 am

    #542258

    May I add, that your approach with a separate table for each month might turn out not the best. It will complicate things enormously.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 15, 2005 at 6:45 am

    #542261

    Thanks a lot Frank, the script is exactly what I was looking for

    As for your recommendation, I feel the same way in a sense but I cannot decide whether or not it's better to keep more than 200 mio records (3 years backdata) in a single table when 90% of the queries will only require data for last two months.

     

  • February 15, 2005 at 6:51 am

    #542263

    Well, you know your requirements better than anybody else.

    I guess someone else with experience with archive systems will respond. Anyway, glad I could help with your other problem.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 16, 2005 at 2:27 am

    #542472

    Hello Karami,

    your table is bigger than those I work with (these have max. about 30M rows), but I still think that splitting it into monthly tables is not a good idea. I would suggest rather to explore ground along the lines of archiving data into another table, or into archive database, after they become static (i.e. you are sure nobody will ever change anything). Solution depends on how often anyone needs to access the data, with special respect to queries that require both old and new data. If 90% queries use only the last 2 months, then there should be no problem.

    It could be a good idea to archive data after 13 months, because frequent question is "how do we fare this month, compared to the same month of previous year", and also there is no chance that someone will need to modify data that old - but again, everything depends on your environment, business processes and requirements, and of course also on your hardware.

    HTH, Vladan

    -----------------------------

    Quoted from http://www.sommarskog.se/dynamic_sql.html:

    Having one table for each month may be necessary in a system like Access or with flat files to get acceptable performance. But with SQL Server or any other high-end RDBMS there is rarely any reason for this. SQL Server and its competitors are designed for handling large amount of data, and for addressing that data with the keys in the data. That is, that year and month are simply the first part of the primary key in your one single sales table.

    You may still not be convinced and think I will have several millions of rows. The database will not work if I have all data in the same table. When you get some 100 million rows, then you might have an issue to address. For this end, SQL 2000 offers features such as partitioned views, and even distributed partitioned views, which permits you to partition big data sets into several tables, but you can still access them as they were one. (Note: I've here talked about number of rows to simplify. What actually matter is the total size of the table, which also depends on the average size of the rows.)

  • February 16, 2005 at 2:45 am

    #542474

    Yes, if I had my way here, I would also opt for one single table.

    Modern RDBMS are highly optimized systems for handling of large numbers of rows. So, the system itself isn't the limitation here. Carefully considering the table structure, the indexes and the app accessing the data is more important. It clearly will take a lot of testing to find the best working solution here.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • February 16, 2005 at 3:04 am

    #542476

    I think partinioned view would be the best solution for your problem as it distributes automatically the data across the table. One problem will still exist: you have to define and add your tables to the view manually.



    Bye
    Gabor

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply