Best way to do this? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Best way to do this?

Author

Topic

KimJ
Starting Member

38 Posts

Posted - 2003-10-07 : 14:24:19

I'm creating two tables, one called 'feedback' the other 'feedback_auditTrail'.

I want certain information from the feedback table to dynamically populate the feedback_audittrail table. this will happen on inserts and updates.

so, when data is inserted/updated in the feedback table, it will also get inserted into the feedback_audittrail table. I thought I could do this with a trigger, but I think I'm wrong (big surprise there).

Any ideas on the best way to do this?

Thanks!

KimJ
Starting Member

38 Posts

Posted - 2003-10-07 : 14:33:01

Also - SQL 7

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-07 : 14:39:04

nope, not wrong...

But why would you want to track inserts...it'll be there for you in your base table...

Just track Updates and Deletes...

Have you coded anything yet?

Search the site for HISTORY...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-07 : 14:42:26

Besides triggers, you could also use a third party tool for this. Not sure if you want to go down this route, but just google "sql server audit" and you'll see a bunch of solutions. You would only want to go down this route if you were going to audit multiple tables since it might be cost beneficial. With one table though, a trigger would be all that is needed.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-07 : 14:44:26

Here's a good link....

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29614

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

KimJ
Starting Member

38 Posts

Posted - 2003-10-07 : 14:50:13

Brett-

the base table will be updated with new values, so the audit trail table will hold the original data, as well as the updates along the way.

thanks for the 'history' tip.

Kim

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-07 : 15:27:18

Glad we could help...and don't be a stranger...

btw...I usually track all changes on every table...

then again, we're only around 2/3 gb across my various projects....have to come up with an archive stategy soon...

good luck!

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-10-07 : 16:38:33

Would this help your logic any?

/**********************************************************************************************
Query Name : qryNotesUpd
Created By : Aj
Date : 01/17/2002
Description : When an existing note is updated this proc looks at the incoming intNoteID
and looks for an existing record in tblNotes. If it finds one, it pushes the existing
record into tblNotesHistory. If the push is successful, the incoming record is allowed to
overwrite the existing record. We are doing this as a way to keep a history of all note updates
in a datawarehouse.
***********************************************************************************************/

CREATE PROCEDURE qryNotesUpd
@intUserId INT = -1,
@intNoteID int,
@intID int,
@intFormID int,
@intPersonID int = NULL,
@intReferralID int = NULL,
@intEnteredByID int,
@intReportedByID int = NULL,
@txtReportedBy nvarchar(100) = NULL,
@dtiEntered datetime = getdate,
@dtiReported datetime = NULL,
@intLocked tinyint = 0,
@intObsolete tinyint = 0,
@txtNote ntext,
@intNoteTypeID int = null

AS

INSERT tblNotesHistory
(intNoteID,
intEnteredByID,
intReportedByID,
txtReportedBy,
dtiEntered,
dtiReported,
intLocked,
intObsolete,
txtNote,
intNoteTypeID
)

SELECT intNoteID,
intEnteredByID,
intReportedByID,
txtReportedBy,
dtiEntered,
dtiReported,
intLocked,
intObsolete,
txtNote,
intNoteTypeID

FROM tblNotes
WHERE intNoteID = @intNoteID

IF @@ERROR > 0
RETURN -1

UPDATE tblNotes SET
intID = @intID,
intFormID = @intFormID,
intPersonID = @intPersonID,
intReferralID = @intReferralID,
intEnteredByID = @intEnteredByID,
intReportedByID = @intReportedByID,
txtReportedBy = @txtReportedBy,
dtiEntered = @dtiEntered,
dtiReported = @dtiReported,
intLocked = @intLocked,
intObsolete = @intObsolete,
txtNote = @txtNote,
intNoteTypeID = @intNoteTypeID
WHERE
intNoteID = @intNoteID

GO

In a nutshell, we store notes within our software system. Notes are not allowed to be "edited or deleted", more on that in a minute. There is always a history to them. It kind of sounds like what you are trying to do.

Instead of notes, you are trying to push some feedback information into a holding table, for what appears to be auditing purposes.

This is an UPDATE proc. So it will only fire if we are editing an existing record. My insert proc is a normal insert proc, I can post it if you would like.

The new data is sent in through the parameters at the top. Before those new parameters overwrite the record, we grab the existing data and shove it into a History table. Then we allow the update to take place as normal. This way, the edits appear to have worked, but we have what the notes originally said should it ever be necessary for legal purposes.

Is this the basic type of logic you are looking for? Hopefully this helps.

Aj

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-07 : 17:08:30

Well....

a procedure doesn't fire...

it gets called.

a trigger fires....

it's like ron popiel...set it and forget it..(which usually happend and you stand there scratching your head goin...what the hell is going , until you remember)

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2003-10-07 : 18:07:28

I want one of those Rotiserie things so badly! I want to set it and forget it!

Aj

KimJ
Starting Member

38 Posts

Posted - 2003-10-08 : 13:54:05

Okay, I don't know what the heck I'm doing.

First of all, the trigger is working fine (sort of) for inserting the data into the audit trail table

Here's my issue, the first table (feedback) has a few triggers -
one to update the insert date and update date to getdate() on new inserts
one to update the update date to getdate() on updates

That worked fine, except I noticed that the info was getting into the audit trail table multiple times - obviously because the change to the date fields was triggering the insert to the audit trail table.

How do I overcome this?

I just need this to happen:
On new inserts - update the insert date and update dates
On updated inserts - update the update date
On new and updated inserts - update the audit trail table.

I tried doing it all in one trigger, but then I got 3 rows in the audit trail table.

I'm losing it!

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-08 : 14:00:36

Go in to Enterprise Manager.

Go to the main table (not the audit) and script it. Make sure you select all of the options.

and post the code here so we can see it.

Sure it just needs a tweak

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

KimJ
Starting Member

38 Posts

Posted - 2003-10-08 : 14:10:51

Okay... here you go! The triggers, you will see, are my attempt to handle the date/time updates at the same time as the insert to the audit trail.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Feedback_AuditTrail_Feedback]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Feedback_AuditTrail] DROP CONSTRAINT FK_Feedback_AuditTrail_Feedback
GO

/****** Object: Trigger dbo.insert_history Script Date: 10/8/2003 2:11:13 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[insert_history]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[insert_history]
GO

/****** Object: Trigger dbo.insert_newhistory Script Date: 10/8/2003 2:11:13 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[insert_newhistory]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[insert_newhistory]
GO

/****** Object: Table [dbo].[Feedback] Script Date: 10/8/2003 2:11:13 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Feedback]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Feedback]
GO

/****** Object: Table [dbo].[Feedback] Script Date: 10/8/2003 2:11:17 PM ******/
CREATE TABLE [dbo].[Feedback] (
[iFeedbackID] [int] IDENTITY (1, 1) NOT NULL ,
[iNotesID] [int] NULL ,
[iCompanyID] [int] NOT NULL ,
[vchFirstName] [varchar] (50) NULL ,
[vchLastName] [varchar] (50) NULL ,
[vchEmail] [varchar] (75) NULL ,
[iSourceID] [int] NOT NULL ,
[iCustomerType] [int] NOT NULL ,
[iStatusID] [int] NOT NULL ,
[iProductID] [int] NOT NULL ,
[iAreaID] [int] NOT NULL ,
[iThemeID] [int] NOT NULL ,
[iFunctionalityID] [int] NOT NULL ,
[vchRelease] [varchar] (50) NULL ,
[vchSummary] [varchar] (500) NULL ,
[vchDetail] [varchar] (4000) NULL ,
[vchRevisions] [varchar] (500) NULL ,
[vchRemarks] [varchar] (4000) NULL ,
[tiNotify] [tinyint] NULL ,
[iInsertBy] [int] NULL ,
[dtInsertDate] [datetime] NULL ,
[iUpdateBy] [int] NULL ,
[dtUpdateDate] [datetime] NULL ,
[tiRecordStatus] [tinyint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Feedback] WITH NOCHECK ADD
CONSTRAINT [PK_Feedback] PRIMARY KEY CLUSTERED
(
[iFeedbackID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Feedback] WITH NOCHECK ADD
CONSTRAINT [DF_Feedback_tiRecordStatus] DEFAULT (1) FOR [tiRecordStatus]
GO

ALTER TABLE [dbo].[Feedback] ADD
CONSTRAINT [FK_Feedback_Codes_Area] FOREIGN KEY
(
[iAreaID]
) REFERENCES [dbo].[Codes_Feedback] (
[iCodeID]
),
CONSTRAINT [FK_Feedback_Codes_Functionality] FOREIGN KEY
(
[iFunctionalityID]
) REFERENCES [dbo].[Codes_Feedback] (
[iCodeID]
),
CONSTRAINT [FK_Feedback_Codes_InsertBy] FOREIGN KEY
(
[iInsertBy]
) REFERENCES [dbo].[Codes_UserIDs] (
[iUserID]
),
CONSTRAINT [FK_Feedback_Codes_Product] FOREIGN KEY
(
[iProductID]
) REFERENCES [dbo].[Codes_Feedback] (
[iCodeID]
),
CONSTRAINT [FK_Feedback_Codes_Source] FOREIGN KEY
(
[iSourceID]
) REFERENCES [dbo].[Codes_Source] (
[iSourceID]
),
CONSTRAINT [FK_Feedback_Codes_Status] FOREIGN KEY
(
[iStatusID]
) REFERENCES [dbo].[Codes_Feedback] (
[iCodeID]
),
CONSTRAINT [FK_Feedback_Codes_Theme] FOREIGN KEY
(
[iThemeID]
) REFERENCES [dbo].[Codes_Feedback] (
[iCodeID]
),
CONSTRAINT [FK_Feedback_Codes_UpdateBy] FOREIGN KEY
(
[iUpdateBy]
) REFERENCES [dbo].[Codes_UserIDs] (
[iUserID]
),
CONSTRAINT [FK_Feedback_Customers] FOREIGN KEY
(
[iCompanyID]
) REFERENCES [dbo].[Customers] (
[iCompanyID]
)
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Trigger dbo.insert_history Script Date: 10/8/2003 2:11:17 PM ******/
CREATE TRIGGER [insert_history] ON dbo.Feedback
FOR INSERT
AS

update feedback set dtInsertDate = (select getdate()) where iFeedbackID = (select iFeedbackID from Inserted)
update feedback set dtUpdateDate = (select getdate()) where iFeedbackID = (select iFeedbackID from Inserted)
INSERT Feedback_AuditTrail
(iFeedbackID, iCompanyID, iCustomerTYpe, iStatusID, iAreaID,
iThemeID, iFunctionalityId, vchSummary, vchDetails, vchRevisions, vchRemarks,
iFBRecordStatus, iInsertBy)
SELECT iFeedbackID, iCompanyID, iCustomerType, iStatusID, iAreaID,
iThemeID, iFunctionalityId, vchSummary, vchDetail, vchRevisions, vchRemarks,
tiRecordStatus, iInsertBy
FROM inserted

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Trigger dbo.insert_newhistory Script Date: 10/8/2003 2:11:17 PM ******/
CREATE TRIGGER [insert_newhistory] ON dbo.Feedback
FOR UPDATE
AS
update feedback set dtUpdateDate = (select getdate()) where iFeedbackID = (select iFeedbackID from Inserted)
INSERT Feedback_AuditTrail
(iFeedbackID, iCompanyID, iCustomerTYpe, iStatusID, iAreaID,
iThemeID, iFunctionalityId, vchSummary, vchDetails, vchRevisions, vchRemarks,
iFBRecordStatus, iInsertBy)
SELECT iFeedbackID, iCompanyID, iCustomerType, iStatusID, iAreaID,
iThemeID, iFunctionalityId, vchSummary, vchDetail, vchRevisions, vchRemarks,
tiRecordStatus, iInsertBy
FROM inserted

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-08 : 14:29:53

Wow...You're updating the table in the trigger that's for that table....

why does this ever come home?

a trigger fires when an update occurs...

So you perform an update

trigger fires

inside trigger...update table (trigger should fire again...)

and you should have an infinite loop

didn't you have to kill the spid?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

KimJ
Starting Member

38 Posts

Posted - 2003-10-08 : 14:31:37

No, I didn't. unless I still have to but just didn't notice!

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-08 : 14:55:20

Guess not..



CREATE TABLE myTable99 (col1 int IDENTITY(1,1), col2 datetime, col3 char(1))
GO

CREATE TRIGGER myTrigger ON myTable99 FOR UPDATE AS UPDATE myTable99 SET col2 = GetDate()
GO

INSERT INTO myTable99 (col3) SELECT 'x'
GO

UPDATE myTable99 SET col3 = 'A'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

Gotta read up on that...

I'm suprised that this works

quote:

update feedback set dtUpdateDate = (select getdate()) where iFeedbackID = (select iFeedbackID from Inserted)

Why not Just Do:

UPDATE Feedback SET dtUpdateDate = GetDate() WHERE iFeedbackID IN (SELECT iFeedBackID FROM inserted)

I never do this is a trigger...you have to perform an update to fire that trigger...why not just supply it to the SQL statement?

And again, why move new inserts to an audit table...they are alread stored in the base.

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

KimJ
Starting Member

38 Posts

Posted - 2003-10-08 : 15:12:35

Now I see what you are saying...
"why move new inserts to an audit table...they are alread stored in the base."

Meaning, that particular information is in both tables twice - should I just do an insert to select the 'old' information into the audit trail table before the row is updated?

Would I be able to do that with a trigger?

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-08 : 15:21:34

Yeah...I would create a view

CREATE VIEW ALL AS
SELECT * FROM BASE
UNION ALL
SELECT * FROM AUDIT

So it would Look like 1 table.

Also I would add something to track deletes...

And understand..if you affect many rows with a single statement, Inserted will have many rows...

It's a set based thing...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-08 : 15:36:26

And actually I think you want Deleted...it contains the old value...the new values are in the base..

Here's a sample you can cut and paste to see how it works...





CREATE TABLE myTable99 (col1 int IDENTITY(1,1), col2 datetime, col3 char(1))
GO
CREATE TABLE myTable00 (col1 int, col2 datetime, col3 char(1), HIST_ADD_DT datetime)
GO

CREATE TRIGGER myTrigger ON myTable99 FOR UPDATE 
AS
	INSERT INTO myTable00 (col1, col2, col3, HIST_ADD_DT)
	SELECT col1, col2, col3, GetDate() FROM deleted
GO

CREATE VIEW myTablexx AS
SELECT col1, col2, col3, null AS HIST_ADD_DT FROM myTable99
UNION ALL
SELECT col1, col2, col3, HIST_ADD_DT  FROM myTable00
GO

INSERT INTO myTable99 (col3) SELECT 'x'
GO

UPDATE myTable99 SET col3 = 'A', col2 = GetDate() WHERE col1 = 1
GO

INSERT INTO myTable99 (col3) SELECT 'y'
GO

UPDATE myTable99 SET col3 = 'B', col2 = GetDate() WHERE col1 = 1
GO


SELECT * FROM myTable99
GO
SELECT * FROM myTable00
GO
SELECT * FROM myTablexx
GO

DROP TABLE myTable99
GO
DROP TABLE myTable00
GO
DROP VIEW myTablexx
GO