How to select the primary key field's value from deleted or inserted in a TRIGGER?

Question

4.00/5 (1 vote)

See more:

I want to create a TRIGGER which can be used for every tabels of a database.
So this TRIGGER's sqlstatement is not changed for a different table.
For example,
DB db_A have 2 tabels, tb_A and tb_B
I want a TRIGGER named Loger to get the primary key field's value from deleted or inserted table.
when I insert a new record into tb_A,
the TRIGGER Loger will get the primary key field's value of the inserted table.
but I don't know the the primary key field's fieldname .This is my condition.
Can I create this TRIGGER with this condition?

CREATE TRIGGER [TRIGGER_NAME] ON [TABLE]
FOR INSERT,UPDATE,DELETE
AS
    IF @@ROWCOUNT = 0
        RETURN ;
    IF NOT EXISTS(SELECT * FROM deleted)
       BEGIN
       --INSERT sqlstatement
        
       END

Posted 9-Apr-11 6:28am

taony

Updated 9-Apr-11 6:30am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2011-04-09T06:42:00

Solution 1

You cannot create a single trigger for several tables. You need to have separate triggers for each table. Because of this you can create for example a stored procedure which you use from the trigger and pass the primary key name along some other information to the procedure. Of course you can also use system tables to fetch the primary key name, but I don't think that would make sense.

Posted 9-Apr-11 6:42am

Wendelius

Comments

taony 9-Apr-11 12:54pm

Thank for your answer.I know I cannot create a single trigger for several tables.I just want to create a same trigger for every tables.

Wendelius 9-Apr-11 13:00pm

I understand, but why. Wouldn't it be simpler to have a slight modification in each trigger, for example passing the primary key name to a procedure (or whatever is needed). If you want to save the amount of work to write the triggers (since you have to make a modification for each of those), why not generate the triggers programmatically?

taony 9-Apr-11 13:10pm

Thank you.Now I will follow your advice.

Wendelius 9-Apr-11 13:19pm

You're welcome :)