Re: Use INSERTED and DELETED in SP
anonymous_at_discussions.microsoft.com
Date: 07/08/04
- Next message: Vijay: "MDX Analysis Services"
- Previous message: Narayana Vyas Kondreddi: "Re: Tracing program execution on production environment, what tools to use?"
- In reply to: Russell Fields: "Re: Use INSERTED and DELETED in SP"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 8 Jul 2004 11:22:55 -0700
Hi Russel,
thx again. Cool idea, but I wanted to create the Triggers
on the fly. So I used (many) variables to store the code
and do an "EXECUT (var1+var2+var3+....)" at the end of my
generator. Not nice, but it works.
Now an administrator can call my "generator SP" that will
notice any structural changes in the DB schema and will
then generate a new trigger for the changed tab. If the
tab was changed and no one called my generator, the
existing trigger notice that and wont write something to
the log.
So, thx again for your answer and have a good weekend.
take care
Achim
many many many
>-----Original Message-----
>Achim,
>
>I did this once in the past, with the following
implementation.
>
>CREATE TABLE #CodeTable (
> RowID INT IDENTITY,
> RowText VARCHAR(255))
>
>I inserted each line of code as I built it into its own
row. Eventually you
>could wind up with hundreds or thousands of lines of
code. (Include lines
>with 'GO' in the table.) Once complete, I could then
>
>SET NOCOUNT ON
>SELECT RowText AS '-- Here is my script' FROM #CodeTable
ORDER BY RowID
>
>The result set would be a complete script, that I could
then execute. You
>could just cut and paste from the results pane in query
analyzer or you
>could put together a couple of OSQL commands to create
the script then
>execute it.
>
>Hope that helps,
>Russell Fields
>
>"Achim Langheinrich" <achim@langheinrich-net.de> wrote in
message
>news:28d7401c464db$6bf7bdb0$a501280a@phx.gbl...
>> Hi Russel,
>>
>> u r right. I want to implement a "long term" log. Speed
>> isn't an issue here, so I don't care about performance.
>>
>> In the meantime I have finished a "generator" SP which
>> build up a trigger create sting and uses sp_executesql
to
>> create the trigger. Problem I have now: for the bigger
>> ones (tables with many cols) the trigger create string
>> grows to more than 4000 chars. :-(
>> What can I do? Create an external file an call ISQL? Any
>> suggestions are appreciated.
>>
>> kind regards
>> Achim
>>
>> >-----Original Message-----
>> >Achim,
>> >
>> >I take it that you are wanting a long-term log of
changes
>> to your system,
>> >not a view into the transaction log. If you need
this, I
>> believe that you
>> >will need a trigger on each table. Remember that there
>> are performance
>> >consequences.
>> >
>> >However, if your definitions are straight-forward you
>> should be able to read
>> >some metadata, either from INFORMATION_SCHEMA views or
>> from your own
>> >metadata tables, run it through a program (T-SQL, VB,
and
>> XML Style Sheet,
>> >etc) and produce the triggers in an automated fashion.
>> In this way the use
>> >of inserted & deleted is internal to your triggers, but
>> you avoid hand
>> >coding each one.
>> >
>> >FWIW,
>> >Russell Fields
>> >"Achim Langheinrich" <achim@langheinrich-net.de> wrote
in
>> message
>> >news:27bf601c4643c$84d8c2b0$a601280a@phx.gbl...
>> >> Hi,
>> >> thx for the quick answers. The reason that I wanted
to
>> >> uses these tables in SP is, that I try to write
>> >> an "universal History Trigger" (UHT). This UHT
should be
>> >> called from various tables and should discover by
>> himself
>> >> (given the name of the calling table) what to store
(any
>> >> changed, inserted or deleted data) into one HISTORY
>> table.
>> >> The HISTORY table has a set of key-fields, a column-
>> field
>> >> and a value field.
>> >>
>> >> CREATE TABLE [dbo].[HISTORY] (
>> >> [LastChangedAt] [datetime] NOT NULL ,
>> >> [LastChangedBy] [varchar] (10) NOT NULL ,
>> >> [TAB_ID] [int] NOT NULL ,
>> >> [WHAT] [char] (3) NOT NULL , -- upd, del or ins
>> >> [KEY1] [varchar] (25) NOT NULL ,
>> >> [KEY2] [varchar] (25) NULL ,
>> >> [KEY3] [varchar] (25) NULL ,
>> >> [KEY4] [varchar] (25) NULL ,
>> >> [COL_ID] [smallint] NOT NULL ,
>> >> [VALUE] [varchar] (7500) NULL
>> >> )
>> >>
>> >> I managed to figured out (dynamically) what structure
>> the
>> >> given table has and what the key-fields are (all
inside
>> >> the trigger). What I was not able to do is to create
a
>> >> SELECT statement on the fly; e.g. SELECT ADR_ID FROM
>> >> INSERTED. Build up a SQL-string and use EXECUTE or
>> >> sp_executesql isn't possible, because INSERTED or
>> DELETED
>> >> are not available then. So I decided to make
>> a "universal
>> >> History Trigger Generator" that creates a TRIGGER for
>> each
>> >> Table that should use the History table. The HISTORY-
>> table
>> >> should be a somehow transaction log, so that you can
>> >> examine any data change at a later date at a single
>> palce.
>> >>
>> >> Any suggestions of
>> >> 1. how to do this in a smarter way
>> >> 2. how to do SELECT statements on the fly without
using
>> >> EXEC or sp_executesql?
>> >> (e.g. SELECT @valVal = <the_first_column> FROM
>> >> <the_fouth_table>)
>> >>
>> >> thx
>> >> Achim
>> >>
>> >>
>> >> >-----Original Message-----
>> >> >You can however insert the contents of the inserted
and
>> >> deleted table into
>> >> >temporary tables and use those temporary tables in
the
>> >> stored procedure, but
>> >> >that is not a route I would advise someone to use. I
>> >> think using stored
>> >> >procedures in triggers is something to be frowned
upon
>> in
>> >> most cases anyway.
>> >> >
>> >> >--
>> >> >Jacco Schalkwijk
>> >> >SQL Server MVP
>> >> >
>> >> >
>> >> >"David Portas"
<REMOVE_BEFORE_REPLYING_dportas@acm.org>
>> >> wrote in message
>> >> >news:Q7qdnYadbaMkSXbd4p2dnA@giganews.com...
>> >> >> No. The virtual tables can be referenced only
within
>> >> the trigger code
>> >> >> itself.
>> >> >>
>> >> >> --
>> >> >> David Portas
>> >> >> SQL Server MVP
>> >> >> --
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>
>.
>
- Next message: Vijay: "MDX Analysis Services"
- Previous message: Narayana Vyas Kondreddi: "Re: Tracing program execution on production environment, what tools to use?"
- In reply to: Russell Fields: "Re: Use INSERTED and DELETED in SP"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
