How to determine the table name within a trigger

Question

How to determine the table name within a trigger

karami

SSC Enthusiast

Points: 112
More actions
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?

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

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

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]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

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]

karami SSC Enthusiast Points: 112 More actions · Answer 3

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.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

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]

Vladan SSC-Insane Points: 22072 More actions · Answer 5

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.)

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

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]

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 7

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