Need to Log update attempt then block that update

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

General SQL Server Forums

New to SQL Server Programming

Need to Log update attempt then block that update

Author

Topic

tubbscc
Starting Member

3 Posts

Posted - 2010-03-24 : 22:37:04

SQL 2000: I have a table Enrolments that I need to log any attempted change to the ResultDate field if ResultDate=5 to another table enrolments_audit. I then need to block that update from happening once logged. Let me know if you need more info and thanks for the help.

--Enrolments Table Design
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Enrolments](
[IDco] [int] NOT NULL DEFAULT (0),
[IDu] [int] NOT NULL DEFAULT (0),
[EnrolDate] [datetime] NOT NULL DEFAULT (getdate()),
[DueDate] [datetime] NULL,
[Result] [smallint] NOT NULL DEFAULT (0),
[ResultDate] [datetime] NULL,
[Type] [smallint] NOT NULL DEFAULT (0),
CONSTRAINT [aaaaaEnrolments_PK] PRIMARY KEY CLUSTERED
(
[IDco] ASC,
[IDu] ASC
)WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]

--Enrolments_Audit Table Design
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Enrolments_Audit](
[IDco] [int] NOT NULL CONSTRAINT [DF__Enrolments__IDco__5BED93EA] DEFAULT (0),
[IDu] [int] NOT NULL CONSTRAINT [DF__Enrolments___IDu__5CE1B823] DEFAULT (0),
[EnrolDate] [datetime] NOT NULL CONSTRAINT [DF__Enrolment__Enrol__5DD5DC5C] DEFAULT (getdate()),
[DueDate] [datetime] NULL,
[Result] [smallint] NOT NULL CONSTRAINT [DF__Enrolment__Resul__5ECA0095] DEFAULT (0),
[ResultDate] [datetime] NULL,
[Type] [smallint] NOT NULL CONSTRAINT [DF__Enrolments__Type__5FBE24CE] DEFAULT (0),
[UpdateAttemptDate] [datetime] NULL
) ON [PRIMARY]

This is my first time writing a trigger and it's been slow going with alot of mistakes along the way. This is the code I have so far that does log the update to enrolments.ResultDate if Result=5 but two problems still.

1. I thought I could use a Rollback Transaction after inserting all deleted values into enrolments_audit but that just undoes/blocks what I just did.

2. When updating the UpdateAttemptDate in enrolments_audit if there are more than one row with the same data except for the UpdateAttempDate all rows with the same data get the same UpdateAttemptDate I just set, I realize I need a qualifier here but not sure what.

Here's my trigger so far, be kind as I've worked really hard on this and it almost works. If what I've done is completely wrong please advise on a better direction.

--Trigger Code
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trig_testEnrolments] ON [dbo].[Enrolments] FOR UPDATE AS
SET NOCOUNT ON
/*PREVENT UPDATES TO 'RESULTDATE' IF 'RESULT' ALREADY = 5
IMPLEMENTED TO RESOLVE UNWANTED CHANGES TO THE RESULTDATE
---ONLY IMPLEMENTED FOR DRIVING SAFETY DB*/
IF UPDATE (ResultDate)
BEGIN
IF EXISTS (SELECT * FROM deleted WHERE Result = 5)
BEGIN

/*** COPY ALL ROWS TO BE UPDATED TO ENROLMENTS_AUDIT TABLE Working now ***/
INSERT enrolments_audit (IDco, IDu, EnrolDate, DueDate, Result, ResultDate, Type)
SELECT IDco, IDu, EnrolDate, DueDate, Result, ResultDate, Type
FROM deleted

/*** DECLARE LOCAL VARIABLES ***/
DECLARE @IDco Int
DECLARE @IDu Int
DECLARE @EnrolDate DateTime
DECLARE @DueDate DateTime
DECLARE @Result SmallInt
DECLARE @ResultDate DateTime
DECLARE @Type SmallInt

/*** SET LOCAL VARIABLES ***/
SET @IDco = (SELECT IDco FROM deleted)
SET @IDu = (SELECT IDu FROM deleted)
SET @EnrolDate = (SELECT EnrolDate FROM deleted)
SET @DueDate = (SELECT DueDate FROM deleted)
SET @Result = (SELECT Result FROM deleted)
SET @ResultDate = (SELECT ResultDate FROM deleted)
SET @Type = (SELECT Type FROM deleted)

/*** LOG DATE/TIME FOR 'UpdateAttemptDate' FOR NEW LOG ENTRY ***/
UPDATE enrolments_audit SET UpdateAttemptDate = GetDate()
WHERE @IDco = enrolments_audit.Idco
AND @IDu = enrolments_audit.IDu
AND @EnrolDate = enrolments_audit.EnrolDate
AND @DueDate = enrolments_audit.DueDate
AND @Result = enrolments_audit.Result
AND @ResultDate = enrolments_audit.ResultDate
AND @Type = enrolments_audit.Type

/*** Print 'Attempt to update ResultDate was STOPPED' ***/
/*** DO SOMETHING TO STOP THE UPDATE HERE ***/
/*** ROLLBACK TRANSACTION ***/

END
END

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-25 : 04:24:49

Look up the syntax for CREATE TRIGGER in Books Online:

CREATE TRIGGER [dbo].[trig_testEnrolments] ON [dbo].[Enrolments] INSTEAD OF UPDATE AS

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-25 : 04:25:28

http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 05:00:10

"/*** SET LOCAL VARIABLES ***/
SET @IDco = (SELECT IDco FROM deleted)
SET @IDu = (SELECT IDu FROM deleted)
SET @EnrolDate = (SELECT EnrolDate FROM deleted)
SET @DueDate = (SELECT DueDate FROM deleted)
SET @Result = (SELECT Result FROM deleted)
SET @ResultDate = (SELECT ResultDate FROM deleted)
SET @Type = (SELECT Type FROM deleted)

/*** LOG DATE/TIME FOR 'UpdateAttemptDate' FOR NEW LOG ENTRY ***/
UPDATE enrolments_audit SET UpdateAttemptDate = GetDate()
WHERE @IDco = enrolments_audit.Idco
AND @IDu = enrolments_audit.IDu
AND @EnrolDate = enrolments_audit.EnrolDate
AND @DueDate = enrolments_audit.DueDate
AND @Result = enrolments_audit.Result
AND @ResultDate = enrolments_audit.ResultDate
AND @Type = enrolments_audit.Type"

Two suggestions:

This only selects one row (in effect at random from DELETED table), so is not safe as the trigger will fire only once for multiple rows deleted in a single batch

Multiple SET statements will be slow, instead use


SELECT @IDco = IDco
       @IDu = IDu
...
       @Type = Type
FROM deleted

this will also ensure that the variables are set to the SAME row. However, you still have the problem that this will only select one row from DELETED (at random).

Not sure what your logic is intended to be here, but you can solve this by just using DELETED in a JOIN


UPDATE U
SET UpdateAttemptDate = GetDate()
FROM enrolments_audit AS U
     JOIN DELETED AS D
         ON D.IDco = U.Idco
        AND D.IDu = U.IDu
        AND D.EnrolDate = U.EnrolDate
        AND D.DueDate = U.DueDate
        AND D.Result = U.Result
        AND D.ResultDate = U.ResultDate
        AND D.Type = U.Type

but I think what you should be doing is to use teh Priamry Key for the join - maybe it is possible all those stated fields are not UNIQUE within the database?


[code]
UPDATE U
SET UpdateAttemptDate = GetDate()
FROM enrolments_audit AS U
     JOIN DELETED AS D
         ON D.MyID = U.MyID

tubbscc
Starting Member

3 Posts

Posted - 2010-03-25 : 13:15:14

quote:
Originally posted by Lumbago

Look up the syntax for CREATE TRIGGER in Books Online:
CREATE TRIGGER [dbo].[trig_testEnrolments] ON [dbo].[Enrolments] INSTEAD OF UPDATE AS

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Lumbago, I did try the INSTEAD OF UPDATE with the same trigger code I posted before. I learned that this prevents any updates to the enrolment table at all regardless of Result. I do want other update to occur, I only want to block updating the ResultDate if the Result is already 5.

I thought initially that the INSTEAD OF UPDATE would see the update attempt and then decide whether to allow or block the update based on this logic:

IF UPDATE (ResultDate)
BEGIN
IF EXISTS (SELECT * FROM deleted WHERE Result = 5)
BEGIN

tubbscc
Starting Member

3 Posts

Posted - 2010-03-25 : 13:18:14

Kristen, I think I see what you are saying here. I will attempt to make the changes you suggest and see if I can get that to work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources