[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

How can access table name and column name in the update trigger

Hi,

I want to get the table name and column name(s) being updated through an update trigger.
Is it possible? If so how?
0
batchakamal
Asked:
batchakamal
  • 5
  • 3
1 Solution
 
geowrianCommented:
When you create the trigger, you specify the table it applies to. If you are using the same trigger on multiple tables and want to differentiate them, you could have the triggers called a stored procedure, and pass the table name (as a string literal) to the stored procedure.

As for the column,you can check for that via:
IF UPDATE(My_Column_Name)

Check here for the full list of trigger options:
http://doc.ddart.net/mssql/sql70/create_8.htm
0
 
batchakamalAuthor Commented:
My triggers are generic and it exists in all the tables.

Eg. I have a table called 'ItemMaster' which contains around 132 columns.

Whenever someone updates an column (ItemUnit) in the table (ItemMaster), the update trigger should send the following values,

Table Name, Column Name, Column Old Value, Column New Value.

I want to know, how can get the Table Name - ItemMaster and Column Name - ItemUnit.
0
 
geowrianCommented:
The best way to do what you are requesting may be to create a stored procedure, then pass the table name and column names to the stored procedure. The table name could be hard-coded in the trigger. The column name could be determined by IF UPDATE(Column) statements. Not very flexible, but I'm not aware of any way to actually retrieve the column name (since it could be several).

Possibly an
IF (COLUMNS_UPDATED())
line could work, but I would avoid that as well.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
brejkCommented:
It's pretty easy to find out the table name in a generic trigger. Just put a code in a trigger:

DECLARE @table_schema sysname, @table_name sysname;
SELECT
  @table_schema = SCHEMA_OBJECT_NAME([parent_id]),
  @table_name = OBJECT_NAME([parent_id])
FROM sys.triggers
WHERE object_id = @@PROCID;

When you have a table name (and its schema) you can query sys.columns for a column list:

SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID(QUOTENAME(@table_schema)+'.'+QUOTENAME(@table_name));

However, I would rather prefer creating individual triggers containing a static T-SQL code for each table (do not use dynamic T-SQL inside the triggers, but build the triggers individually for each table).

You can use a DDL trigger for creating the appropriate triggers each time a table is created or altered.
0
 
geowrianCommented:
@brejk
That's a wonderful query there to retrieve the table name. I would not have thought to do it that way. However, how does it identify the column(s) that are being updated?
0
 
brejkCommented:
@geworian: It does not identify the columns that are being updated. It only allows to to return the table name in a trigger. Use UPDATE function you mentioned to check if any column is being updated. If you use my query to retrieve the table name you will probably use some dynamic T-SQL to build all necessary IF UPDATE conditions (see my second query to find the names of the columns that need to be checked with UPDATE function).

In case you need to generate the triggers automatically I would suggest the following approach:
1. create a stored procedure that accepts table name or table's object_id as a parameter
2. in this procedure build trigger's code dynamically (build CREATE TRIGGER statement and execute it;  using table name / table's id you can easily get all columns metadata) - but the final trigger's code should be static (no dynamic T-SQL in the trigger code)
3. execute the procedure for each table which requires auditing or if you need to audit all tables create a DDL trigger firing on CREATE_TABLE and ALTER_TABLE events and call your stored procedure (create trigger) in this DDL trigger.
0
 
geowrianCommented:
@brejk
Thanks for the information. I was thinking I missed something in your comment on how to retrieve the column name since my answer was not chosen as part of the solution (accessing the column name).
0
 
brejkCommented:
@geworian: I definitely think your answer should be chosen as a part of the solution. It's the UPDATE function that allows to detect which columns are being updated.
0
 
geowrianCommented:
Not a problem - I just thought I missed something.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now