Re: Triggers and Data Driven Query
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 06/29/04
- Next message: David Fuller: "Re: Triggers and Data Driven Query"
- Previous message: FLX: "Can't create more than 256 partitions?"
- In reply to: David Fuller: "Re: Triggers and Data Driven Query"
- Next in thread: David Fuller: "Re: Triggers and Data Driven Query"
- Reply: David Fuller: "Re: Triggers and Data Driven Query"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 28 Jun 2004 20:43:49 -0400
I figured it was some kind of audit trail but I couldn't determine how this:
bstring(COLUMNS_UPDATED(),(@pos-1)/8 + 1 ,1) = power(2, (@pos-1) did what I
think you were after? In any case the information seems pretty useless to
me. All you know is that a column was changed and not who did it or what
the values were changed from and to. But I don't know the whole story
either<g>. You state you can see the trigger firing but you still don't say
what is wrong. Are you not getting any rows int eh history table or are they
the wrong ones or what?
-- Andrew J. Kelly SQL MVP "David Fuller" <DavidFuller@discussions.microsoft.com> wrote in message news:AA2A6C81-8EE4-43E8-B150-77BEB7212D05@microsoft.com... > It is an Audit Trail, more or less. > The audit table (TRSYNCHIST) has a tablename, recid, columnname, datetime, and flag to indicate update, delete, insert. Essentially, what it does is as follows > for each field in table schema > if updated(field) then > write record to TRSYNCHIST > end if > end for > Should I be approaching this differently? > "Andrew J. Kelly" wrote: > > > David, > > > > I don't understand what are you trying to do with the trigger? Can you > > tell me what it is you are trying to accomplish and why you think it is not > > working? > > > > -- > > Andrew J. Kelly SQL MVP > > > > > > "David Fuller" <DavidFuller@discussions.microsoft.com> wrote in message > > news:5CAC1298-BD25-4456-BC6A-E7B910249A77@microsoft.com... > > > You are correct it is firing, I checked via a trace. What do you mean the > > trigger must handle multiple rows? > > > > > > Here is my trigger: Any help would be greatly appreciated. > > > > > > CREATE Trigger UARTerms on ARTerms > > > FOR UPDATE AS > > > DECLARE @FIELD VARCHAR(128) > > > DECLARE @POS INTEGER > > > DECLARE @TABLE VARCHAR(128) > > > DECLARE FIELDS CURSOR FOR > > > select TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION from > > test.INFORMATION_SCHEMA.COLUMNS > > > WHERE TABLE_NAME = 'ARTerms' > > > OPEN FIELDS > > > FETCH NEXT FROM FIELDS INTO @TABLE, @FIELD, @POS > > > WHILE @@FETCH_STATUS = 0 > > > BEGIN > > > if substring(COLUMNS_UPDATED(),(@pos-1)/8 + 1 ,1) = power(2, (@pos-1) > > % 8) > > > BEGIN > > > insert into TRSYNCHIST (RECID, TABLENAME, COLUMNNAME, MODIFYTIME, FLAG) > > > select ins.FormID, @TABLE, @FIELD, GetUTCDate(), 2 From inserted ins > > > END > > > FETCH NEXT FROM FIELDS INTO @TABLE, @FIELD, @POS > > > END > > > CLOSE FIELDS > > > DEALLOCATE FIELDS > > > "Andrew J. Kelly" wrote: > > > > > > > How do you know they are not firing? Remember triggers only fire once > > per > > > > Update not once per row. Your trigger must be able to handle any cases > > > > where multiple rows get updated at once. > > > > > > > > -- > > > > Andrew J. Kelly SQL MVP > > > > > > > > > > > > "David Fuller" <DavidFuller@discussions.microsoft.com> wrote in message > > > > news:194C1933-F4CB-4132-AF0C-23C38E357476@microsoft.com... > > > > > I have a DTS package with a Data Definition query with triggers on the > > > > tables in which data is being applied. It seems that the only triggers > > > > which work are insert triggers. The update triggers do not fire. > > Unlike > > > > Data Transformations there is no option to turn off fast load. Any > > ideas? > > > > > > > > > > > > > > > > > >
- Next message: David Fuller: "Re: Triggers and Data Driven Query"
- Previous message: FLX: "Can't create more than 256 partitions?"
- In reply to: David Fuller: "Re: Triggers and Data Driven Query"
- Next in thread: David Fuller: "Re: Triggers and Data Driven Query"
- Reply: David Fuller: "Re: Triggers and Data Driven Query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
