Finding out primary keys at runtime is not a good idea. As I pointed out in
my previous post, the primary key of a table is not likely to change, so you
can just hard code the trigger. What you really want to do is not to have to
write the code for all the triggers. The way to do that in T-SQL is not to
use generic code as in other language, but to generate the code from the
system tables or information_schema views. If you run the code below in
Query Analyzer you will get a skeleton trigger for every table in your
database and you can expand to create your triggers:
SELECT 'CREATE TRIGGER trga_i_' + table_name + ' ON ' + table_name + ' AFTER
INSERT'
+ CHAR(13) + 'AS' + CHAR(13) + 'IF @@ROWCOUNT = 0 RETURN' + CHAR(13) + 'GO'
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
For the schema of the information_schema views see:
http://www.dbmaint.com/download/info_schema/information_schema_views_all.jpg Also read the following article about numbering rows:
HOW TO: Dynamically Number Rows in a Select Statement:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q186133 --
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
[quoted text, click to view] "Neo" <gogineni_007@hotmail.com> wrote in message
news:160201c37859$c855bb30$a601280a@phx.gbl...
> hi Jacco,
> i am looking at finding primary key at run time
> because , given a table name , i am trying to create a
> trigger on the table and need to track changes on the
> table.basicaaly trying to achive the rowid of oracle
> functionality in sql server.
> Regards
> -NEO
> >-----Original Message-----
> >Hi Neo,
> >
> >Why do you have to find the column(s) for your primary
> key dynamically? A
> >primary key is one of the most important "properties" of
> a table and it is
> >not very likely to change.
> >
> >If you are using an IDENTITY column as a primary key you
> can get the last
> >value that has been inserted in the table by the current
> connection with
> >@@IDENTITY .
> >
> >--
> >Jacco Schalkwijk MCDBA, MCSD, MCSE
> >Database Administrator
> >Eurostop Ltd.
> >
> >
> >"Neo" <gogineni_007@hotmail.com> wrote in message
> >news:040f01c37824$40c56560$a301280a@phx.gbl...
> >> In one of my SQL Server Trigger I have written code like
> >> this.What i am doing is getting the primary key(s) of
> the
> >> table by calling sp_pkeys by passing the table name and
> >> insert the data in to #TPKEYS , i am getting the
> primary
> >> key cloumn name using
> >> SELECT COLUMN_NAME FROM #TPKEYS and assign that value
> in
> >> to @V_KEY1 .i need to use this @V_KEY1 value to get
> the
> >> inserted value from inserted table and assign in to a
> >> variable @V_DATAKEY ,the statement is SELECT @V_KEY1
> >> FROM INSERTED ,but i am not getting the inserted value
> >> for the column value contained in @V_KEY1 in the
> variable
> >> @V_DATAKEY
> >> i tried with both the options to get the inserted value
> >> with out any success.What is the mistake i am doing?
> >> the code as follows
> >>
> >> SET @V_KEY1 = (SELECT COLUMN_NAME FROM #TPKEYS )
> >> SET @V_DATAKEY = (SELECT @V_KEY1 FROM INSERTED ) -- 1
> st
> >> option
> >> SET @V_DATAKEY = ( 'SELECT '+@V_KEY1 +' FROM
> >> INSERTED ) - 2nd option
> >>
> >> Regards
> >> -Neo
> >
> >
> >.
> >
