Problem with trigger
-
Hi All,
SQL Server 2000
Can someone help me with the following - some background info first: -
I have two tables (one to many relationship)
tblTree
intTreeID
dtLastInsepctionDate
intInspectionCycle
strInspectionUnit
dtNextInspectionDate
tblInspection
intInspectionID
intTreeID
dtInspectDate
Whenever a new inspection is added or an existing one edited (inspection date changed) or deleted, I need to check to get the highest inspection date for the parent tree and update the parent tree with that date (dtLastInspectionDate), then calculate the next inspection date due.
I tried creating the following trigger for insert and update/delete quering the inserted and deleted tables but it doesn’t work. Can someone shed some light please.
Thanks
CCB
CREATE TRIGGER inspection_insert_parent_tree ON [dbo].[tblInspection]
FOR INSERT
AS
Declare @intTreeID int
Declare @MaxDate DateTime
Declare @intInspectionCycle int
Declare @strInspectionUnit varchar(50)
Declare @dtNextInspectionDate datetime
Declare changed_cursor Cursor For Select Distinct intTreeID From Inserted
Open changed_cursor
Fetch Next From changed_cursor InTo @intTreeID -- Perform the first fetch.
WHILE @@FETCH_STATUS = 0 -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
Begin -- This is executed as long as the previous fetch succeeds.
Select @MaxDate = Max(A.dtInspectDate)
From tblInspection A
Where A.intTreeID = @intTreeID
If Not @MaxDate = Null
Begin
Select @intInspectionCycle = A.intInspectionCycle,
@strInspectionUnit = A.strInspectionUnit
From tblTree A
Where A.intTreeID = @intTreeID
If @intInspectionCycle <> Null and @strInspectionUnit <> Null
Begin
Set @dtNextInspectionDate =
Case @strInspectionUnit
When 'Days' Then DATEADD(Day, @intInspectionCycle, @MaxDate)
When 'Weeks' Then DATEADD(Week, @intInspectionCycle, @MaxDate)
When 'Months' Then DATEADD(Month, @intInspectionCycle, @MaxDate)
When 'Years' Then DATEADD(Year,@intInspectionCycle, @MaxDate)
Else Null
End
Update tblTree
Set dtLastInspectionDate = @MaxDate,
dtNextInspectionDate = @dtNextInspectionDate
Where intTreeID = @intTreeID
End
End
Fetch Next From changed_cursor InTo @intTreeID
End
Close changed_cursor
Deallocate changed_cursor
-
Hi CCB,
When you say 'it doesn't work' - are you getting anything at all? Error msg's etc?
I take it you realised the above trigger only handles Insert?
Personally - I would fire it on all three (FOR DELETE,INSERT,UPDATE) and then handle each action, eg:
DECLARE @ACT CHAR(6)
DECLARE @del BIT
DECLARE @ins BIT
SET @del = 0
SET @ins = 0
IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @del=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @ins = 1
IF @ins = 1 AND @del = 1 SET @ACT = ''UPDATE''
IF @ins = 1 AND @del = 0 SET @ACT = ''INSERT''
IF @del = 1 AND @ins = 0 SET @ACT = ''DELETE''
Just use @Act to interpret if it was an Ins, Up or Del and act on it accordingly.
Have fun
Steve
We need men who can dream of things that never were.
-
Hi Steve,
I have a seperate one for update and delete, but point taken.
I wrapped it in a cursor in case more than one record is updated at once.
When I say it doesn't do anything, I mean that the parent record never gets updated though it doesn't generated any errors.
Any suggestions
Thanks
CCB
-
The problem appears to be with the checks for nulls. If I remove them, the trigger fires correctly. Any suggestions anyone ?
Thanks CCB
-
Fixed it, should be using is not null.
Trigger now looks as follows:
CREATE TRIGGER inspection_insert_parent_tree ON [dbo].[tblInspection]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @ACT CHAR(6)
DECLARE @del BIT
DECLARE @ins BIT
SET @del = 0
SET @ins = 0
IF EXISTS (SELECT TOP 1 1 FROM DELETED) SET @del=1
IF EXISTS (SELECT TOP 1 1 FROM INSERTED) SET @ins = 1
IF @ins = 1 AND @del = 1 SET @ACT = 'UPDATE'
IF @ins = 1 AND @del = 0 SET @ACT = 'INSERT'
IF @del = 1 AND @ins = 0 SET @ACT = 'DELETE'
Declare @intTreeID int
Declare @MaxDate DateTime
Declare @intInspectionCycle int
Declare @strInspectionUnit varchar(50)
Declare @dtNextInspectionDate datetime
If @ACT = 'UPDATE' or @ACT = 'DELETE'
Select @intTreeID = intTreeID From Deleted
Else
Select @intTreeID = intTreeID From Inserted
Select @MaxDate = Max(A.dtInspectDate)
From tblInspection A
Where A.intTreeID = @intTreeID
Print @MaxDate
If @MaxDate is not Null
Begin
Select @intInspectionCycle = A.intInspectionCycle,
@strInspectionUnit = A.strInspectionUnit
From tblTree A
Where A.intTreeID = @intTreeID
Print @MaxDate
Print @intInspectionCycle
Print @strInspectionUnit
If @intInspectionCycle is not Null and @strInspectionUnit is not Null
Begin
Set @dtNextInspectionDate =
Case @strInspectionUnit
When 'Days' Then DATEADD(Day, @intInspectionCycle, @MaxDate)
When 'Weeks' Then DATEADD(Week, @intInspectionCycle, @MaxDate)
When 'Months' Then DATEADD(Month, @intInspectionCycle, @MaxDate)
When 'Years' Then DATEADD(Year,@intInspectionCycle, @MaxDate)
Else Null
End
Update tblTree
Set dtLastInspectionDate = @MaxDate,
dtNextInspectionDate = @dtNextInspectionDate
Where intTreeID = @intTreeID
End
End
Does this look ok. Ideally, when updating, I should only really fire it when the next inspection date is changed. Any suggestions here ?
Will this work OK if a change is made to multiple inspections by the user ?
thanks for your patience
CCB
-
Hi CCB,
Just got to the same spot - using IS NOT NULL........
From what I can see it should work fine with multiple inspections.
Under update - If you check the next inspection date in the deleted table against next inspection date in the inserted table, you can make the decision what you want to do with the data.
Have fun
Steve
We need men who can dream of things that never were.
-
I am sorry to be the BAD news guy here but that Trigger can't handle multiple records!
you should change the logic to a set based DML if you want it to be successful.
Noel
* Noel -
Hi Noeld,
Can you elaborate on this (set based DML) ?
Can I put it back into a cursor and set throught the changed records ?
Thanks
CCB
-
Hi Noel,
How goes it?
Go on then - for what reason would it not stand mutiple records?
Speak to you soon
Steve
We need men who can dream of things that never were.
-
Sorry I had to do some stuff:
I need probably some sample data to give an adecuate solution but should be something like:
Update t Set dtLastInspectionDate = A.MaxDate,
dtNextInspectionDate =
(Case t.strInspectionUnit
When 'Days' Then DATEADD(Day, t.intInspectionCycle, A.MaxDate)
When 'Weeks' Then DATEADD(Week, t.intInspectionCycle, A.MaxDate)
When 'Months' Then DATEADD(Month, t.intInspectionCycle, A.MaxDate)
When 'Years' Then DATEADD(Year,t.intInspectionCycle, A.MaxDate)
Else Null
End)
from tblTree t
join
(Select intTreeID, Max(dtInspectDate) as MaxDate
From tblInspection
group by intTreeID) A on A.intreeID = T.TreeID
join
inserted new on new.intTreeID = A.intTreeID -- Change this for deleted when a Delete occurs ???
where
t.intInspectionCycle is not Null
and
t.strInspectionUnit is not Null
Steve, when you run soemthing like:
Select @intTreeID = intTreeID From Inserted
and you have multiple records on the inserted table what value is @intTreeID going to receive? the first, the second,...?
hth
* Noel -
Hi Noel,
just tested that and it work perfectly.
Thanks and thanks to Steve too.
Regards
CCB
-
"and you have multiple records on the inserted table what value is @intTreeID going to receive? the first, the second,...?"
...the last (just in case the newbies come up on this thread
). -
>>...the last (just in case the newbies come up on this thread
).<<Well, the problem is that in SQL there is no concept of First or Last
therefore on some situations you would get the "last" on others you may get another. ... unless you use order by
* Noel -
The last of that set... the last is still the last even if there's no order by
. -
Are you really saying that?
There is no last or first on a set! There is no guarrantie on order on a set. sometimes one value will be the "last" sometimes another value will be returned therefore you can not determine which is the "last"
* Noel
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply