Please explain what this Trigger query does

Question

Post reply

Please explain what this Trigger query does

Viewing 15 posts - 46 through 60 (of 60 total)

You must be logged in to reply to this topic. Login to reply

lawlietdba SSC Enthusiast Points: 199 More actions · Answer 1

Hi Jeff,

Sure ..... 🙂

I am going to implement this and will let you know the status shortly .....

David Burrows SSC Guru Points: 65144 More actions · Answer 2

Well done Jeff 🙂

Please clarify

You are auditing 'what was' and not 'what is'

'Insert' is the first audit row or the main row if no audit exists

and this needs to be catered for in the 'Audit View' if inserts need to be reported

If a row is inserted and then deleted the audit view needs to duplicate the audit row if insert and delete is required to be shown separately.

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 3

David Burrows (1/26/2015)
Well done Jeff 🙂
Please clarify
You are auditing 'what was' and not 'what is'
'Insert' is the first audit row or the main row if no audit exists
and this needs to be catered for in the 'Audit View' if inserts need to be reported
If a row is inserted and then deleted the audit view needs to duplicate the audit row if insert and delete is required to be shown separately.

🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lawlietdba SSC Enthusiast Points: 199 More actions · Answer 4

David Burrows (1/26/2015)
If a row is inserted and then deleted the audit view needs to duplicate the audit row if insert and delete is required to be shown separately.

Hi Jeff,

Is this a concern to be taken for consideration on the new trigger ...?

Do we have to show the insert and delete separately.

Creating a UNION ALL query as you as you advised should suffice right..?

Please clarify

lawlietdba SSC Enthusiast Points: 199 More actions · Answer 5

Hi Jeff,

Please explain how to create a Reporting view for Auditing using Employee table and Employee Audit Table.

Thanks

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 6

lawlietdba (1/27/2015)
David Burrows (1/26/2015)
If a row is inserted and then deleted the audit view needs to duplicate the audit row if insert and delete is required to be shown separately.
Hi Jeff,
Is this a concern to be taken for consideration on the new trigger ...?
Do we have to show the insert and delete separately.
Creating a UNION ALL query as you as you advised should suffice right..?
Please clarify

You already know the answer to this. How did I say INSERTs would be audited? Do you remember any certain columns that I was making absolutely sure got populated in the Employee table? And how much of the data was changed if we did an insert of a row, did [font="Arial Black"]no [/font]updates, and then did a delete? Is there anything on that single deleted row that's different than it was when it was first inserted and is there anything on that row that tells you when it was first inserted. Think about it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 7

David Burrows (1/26/2015)
Well done Jeff 🙂
Please clarify
You are auditing 'what was' and not 'what is'
'Insert' is the first audit row or the main row if no audit exists
and this needs to be catered for in the 'Audit View' if inserts need to be reported
If a row is inserted and then deleted the audit view needs to duplicate the audit row if insert and delete is required to be shown separately.

Sorry David. Was taking a break.

As I explained to the OP, there have to be a couple of columns in the Employee table that will record the creation date/time (which is the insert) and the person/thing that created the row. Those two columns will be reflected in every row that ends up in the audit table. If a given row never ends up in the audit table, then where is the original row? In the original table (Employee table). The reporting view should be a UNION ALL between the Employee table and the EmployeeAudit table. Inserts for rows that have never been changed or deleted will be in the Employee table and Inserts that have been updated or deleted will show up as the first row (temporarily speaking) in the reporting view for any given PK. So, as you said, the reporting view will need to be written to cater to that. Since rows that have only suffered INSERTs won't appear in the audit table, nor will they carry the 3 leading columns of the audit table, I'll just bet that you could figure out that the Employee part of the reporting view is going to have to make an aliased substitution from the two columns that I insisted be in the Employee table to mark the creation of the row.

The same holds true for the current state of non-deleted rows. Where will those always be? In the Employee table. And, yes, the view is going to have to take that into consideration, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

lawlietdba (1/27/2015)
Hi Jeff,
Please explain how to create a Reporting view for Auditing using Employee table and Employee Audit Table.
Thanks

See what I just wrote above. The best way to start it would be to write down the rules for where the original insert lives (2 possibilities there), where the "current" row actually lives (again, two possibilities there), and how to mark those so they come out in the correct order temporally speaking, along with any rows that contain previous statuses (only one place for those).

Give it a try and remember to "peel just on potato at a time" so that you don't drive yourself nuts. I'll give you a hint... you'll need to reference each of the two tables more than once bit it's worth it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

lawlietdba SSC Enthusiast Points: 199 More actions · Answer 9

Hi Jeff ,

Sure 🙂

I got little confused on the last question asked by David..

I am on my way to office and will try your suggestion and post my query here shortly...

Thanks very much ..

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 10

Take your time. It's 1:15AM here. I'm going to bed.:-D

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65144 More actions · Answer 11

Jeff Moden (1/27/2015)
David Burrows (1/26/2015)
Well done Jeff 🙂
Please clarify
You are auditing 'what was' and not 'what is'
'Insert' is the first audit row or the main row if no audit exists
and this needs to be catered for in the 'Audit View' if inserts need to be reported
If a row is inserted and then deleted the audit view needs to duplicate the audit row if insert and delete is required to be shown separately.
Sorry David. Was taking a break.
As I explained to the OP, there have to be a couple of columns in the Employee table that will record the creation date/time (which is the insert) and the person/thing that created the row. Those two columns will be reflected in every row that ends up in the audit table. If a given row never ends up in the audit table, then where is the original row? In the original table (Employee table). The reporting view should be a UNION ALL between the Employee table and the EmployeeAudit table. Inserts for rows that have never been changed or deleted will be in the Employee table and Inserts that have been updated or deleted will show up as the first row (temporarily speaking) in the reporting view for any given PK. So, as you said, the reporting view will need to be written to cater to that. Since rows that have only suffered INSERTs won't appear in the audit table, nor will they carry the 3 leading columns of the audit table, I'll just bet that you could figure out that the Employee part of the reporting view is going to have to make an aliased substitution from the two columns that I insisted be in the Employee table to mark the creation of the row.
The same holds true for the current state of non-deleted rows. Where will those always be? In the Employee table. And, yes, the view is going to have to take that into consideration, as well.

Thanks again Jeff 🙂

Just making sure I got all my ducks in a row so to speak :hehe:

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 12

You bet, David. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27852 More actions · Answer 13

Hi Jeff, from reading your thoughts I hope I understood all of the points you made. I drew out here a base and audit table as

CREATE Table table1

(

IDINT IDENTITY(1,1) NOT NULL,

[ENTRY]VARCHAR(30),

EnteredDateTimeDATETIME,

EnteredByVARCHAR(30)

)

/* ======================================================== */

/* AUDIT table I imagine will appear as follows ... */

CREATE Table table1Audit

(

/* original columns from the base table */

IDINT IDENTITY(1,1) NOT NULL,

[ENTRY]VARCHAR(30),

EnteredDateTimeDATETIME,

EnteredByVARCHAR(30),

/* NEW COLUMNS for audit table */

AUDITEnteredDateTime DATETIME, /* equals enteredDateTime on the updated column */

AUDITEnteredByVARCHAR(30),/* equals enteredBy on the updated column */

ChangeTypeCHAR(1)

)

Is this about you mean? I refer mainly to the added AUDIT columns in the audit table. I think the view to join the two tables can leave out the two Audit columns and simply hard code a 'I' in the base table for the changeType column.

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 14

@MMartin1
Oh boy! Sometimes these posts get lost with all the emails I get. I was reviewing this for other reasons and was embarrassed to find a 2 year old post that I missed.

Yes. That's what I meant in both cases. If you wanted to get clever, you could have 2 columns for date and time to turn this and the original table into a "Pure Type 6 Implementation" of Slowly Changing Dimensions ( https://en.wikipedia.org/wiki/Slowly_changing_dimension ). which enables you to return what the values in the table were for all rows at any given point in time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27852 More actions · Answer 15

Heh, No worries at all Jeff. I am impressed that you found this! I had long forgotten about this topic. It is quite a busy world out there, no embarrassment necessary. I admire your hard work on this forum. Thanks.

----------------------------------------------------