December 15, 2011 at 2:17 am
CREATE TRIGGER tr_1 ON dbo.mytablename
AFTER UPDATE,INSERT
AS
INSERT INTO table1(col1)
SELECT InsRec.col1
FROM
INSERTED Ins
--Below i am calling one sp for which i have to pass the table name
EXEC myspname 'mytablename'
In the above trigger,presently i am hard coding the tablename
but is it possible to get the table name dynamically on which the trigger is defined in order to avoid hard coding the table name
i need to get the tablename dynamically without hard coding to avoid manual errors
December 15, 2011 at 2:50 am
the trigger (e.g. tr_1) relates to one parent table (dbo.mytablename). If you have 10 tables you would need 10 triggers, and each could be hardcoded to "know" about their parent table.
If you execute a statement that touches many rows e.g. UPDATE hits 20 rows, then the trigger will fire once and INSERTED pseudo-table will also have 20 rows. Perhaps this is your intention to populate the common temp1 table with the table name and the particular PK
It is possible to discover the sproc (search BOL for @@PROCID) and relate to parent (via sys.objects.parent_object_id), but I recommend against as each trigger will need to be aware of the underlying table metadata (eg col1 and othercol in my examples below). Trying to have single uberproc (one size fits all) to conjure these at runtime is scary.
HTH
Dick
congrats to Jeff Moden for winning X-DBA !
CREATE TRIGGER tr_1 ON dbo.mytablename
AFTER UPDATE,INSERT
AS
INSERT INTO table1(tablename, pkset)
SELECT 'mytablename',I.col1
FROM INSERTED I
GO
CREATE TRIGGER tr_1 ON dbo.othertablename
AFTER UPDATE,INSERT
AS
INSERT INTO table1(tablename, pkset)
SELECT 'othertablename',I.anothercol
FROM INSERTED I
GO
December 15, 2011 at 2:55 am
December 15, 2011 at 6:28 am
Correct - tables, triggers, sprocs etc should be solid objects. IMHO it is bad practice to create new objects by existing objects (i.e. dynamic DDL).
but in any case, you don't need to do any such thing. You have several tables (2 as in my example) that you want to "watch" via triggers (also 2) , noting changes in that common activity table. But note that each table is different (likely to have different key columns) so each trigger has to do the necessary.
If you have a third table that has a PK of 2 columns, then you would need to combine these in some ugly transform e.g.
convert(varchar,pkcol1)+'_'+convert(varchar,pkcol2)
hence this one-size-fits-all gets nasty very quickly.
Better to have single audit table per subject table (e.g. mytable_adt) with same column metadata plus who+when+what+where and usually save the previous values (i.e. DELETED not INSERTED).
If that is what you want (a frequent business/audit requirement to track who did what etc) then you should look at the SQL2008 Change Data Capture (CDC) etc [rather than roll your own]. Read up in BOL !
December 15, 2011 at 8:15 am
lookatjks (12/15/2011)
...but is it possible to get the table name dynamically on which the trigger is defined in order to avoid hard coding the table name...
SELECT OBJECT_NAME(parent_object_id)
FROM sys.objects
WHERE name = OBJECT_NAME(@@PROCID)
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 30, 2011 at 11:12 am
you should realise that name alone is insufficient (there are many roses!), and you should understand significance of object_id
if you _need_ to discover the parent object you should execute this code instead
SELECT OBJECT_NAME(parent_object_id)
FROM sys.objects
WHERE [object_id]=@@PROCID
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply