July 3, 2008 at 12:02 pm
I have to create a trigger to do some very simple updates on one of our tables. It is so easy the solution is probably staring me in the face but I just can’t see it. When the columns DDELIVERED, DMOVEOUT or DMOVEIN change, DBEGVACANT needs to get updated for future months. The code below is the trigger I wrote that is not working (table definition and sample data follows):
CREATE TRIGGER trUpdateBeginVacant
ON propbut_mi
FOR INSERT, UPDATE
AS
IF (UPDATE(DDELIVERED) OR UPDATE(DMOVEIN) OR UPDATE(DMOVEOUT))
BEGIN
DECLARE @delivered INT
DECLARE @moveout INT
DECLARE @movein INT
DECLARE @month VARCHAR(2)
DECLARE @property NUMERIC(18,0)
SELECT @delivered = ISNULL(DDELIVERED, 0),
@moveout = ISNULL(DMOVEOUT, 0),
@movein = ISNULL(DMOVEIN, 0),
@month = LEFT(SMONTH,2),
@property = HCODE
FROM inserted
UPDATE propbut_mi
SET DBEGVACANT = (ISNULL(DBEGVACANT, 0)+@delivered+@moveout-@movein)
WHERE HCODE = @property AND LEFT(SMONTH,2) > @month
END
CREATE TABLE [dbo].[propbut_mi](
[HMY] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[HCODE] [numeric](18, 0) NULL,
[SCODE] [varchar](12) NULL,
[SNAME] [varchar](30) NULL,
[SMONTH] [varchar](11) NULL,
[DDELIVERED] [numeric](6, 0) NULL,
[DBEGVACANT] [numeric](6, 0) NULL,
[DMOVEIN] [numeric](6, 0) NULL,
[DMOVEOUT] [numeric](6, 0) NULL,
[DRENEWAL] [numeric](6, 0) NULL,
[DAPPLICATION] [numeric](6, 0) NULL,
[DAVGRENT] [numeric](18, 2) NULL,
[DOCCPCT] [numeric](6, 2) NULL
) ON [PRIMARY]
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'00',' ','00 Sep-LY',130,15,1,0,0,0,500.00,91.85)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'01',' ','01 Oct-PY',0,0,10,2,3,0,510.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'02',' ','02 Nov-PY',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'03',' ','03 Dec-PY',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'04',' ','04 Jan',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'05',' ','05 Feb',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'06',' ','06 Mar',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'07',' ','07 Apr',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'08',' ','08 May',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'09',' ','09 Jun',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'10',' ','10 Jul',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'11',' ','11 Aug',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'12',' ','12 Sep',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'13',' ','13 Oct',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'14',' ','14 Nov',0,0,0,0,0,0,0.00,100.00)
INSERT INTO [Budget].[dbo].[propbut_mi] ([HCODE],[SCODE],[SNAME],[SMONTH],[DDELIVERED],[DBEGVACANT],[DMOVEIN]
,[DMOVEOUT],[DRENEWAL],[DAPPLICATION],[DAVGRENT],[DOCCPCT])
VALUES (333,'15',' ','15 Dec',0,0,0,0,0,0,0.00,100.00)
When I pull the code out and run it outside of the trigger, providing the data for the variables, it works as expected so I believe my UPDATE statement is correct as follows:
DECLARE @delivered INT
DECLARE @moveout INT
DECLARE @movein INT
DECLARE @month VARCHAR(2)
DECLARE @property NUMERIC(18,0)
SELECT @delivered = ISNULL(null, 0),
@moveout = ISNULL(90, 0),
@movein = ISNULL(0, 0),
@month = '05',
@property = 333
UPDATE propbut_mi
SET DBEGVACANT = (ISNULL(DBEGVACANT, 0)+@delivered+@moveout-@movein)
WHERE HCODE = @property AND LEFT(SMONTH,2) > @month
The problem I’m having is the trigger does not appear to be firing. I have another trigger on the table which is working perfectly:
CREATE TRIGGER trUpdateOccupancyPercent
ON propbut_mi
FOR INSERT, UPDATE
AS
IF (UPDATE(DBEGVACANT))
BEGIN
DECLARE @totalUnits INT
SELECT @totalUnits = pf.SPROPINFO
FROM PROP_FIELD pf
INNER JOIN inserted AS i ON pf.HPROP = i.HCODE
WHERE ICOUNTER = 4
IF @totalUnits = 0
BEGIN
UPDATE propbut_mi
SET DOCCPCT = 0
FROM propbut_mi AS p
INNER JOIN inserted AS i ON p.HMY = i.HMY
END
ELSE
BEGIN
UPDATE propbut_mi
SET DOCCPCT = ((@totalUnits-p.DBEGVACANT)/@totalUnits)*100
FROM propbut_mi AS p
INNER JOIN inserted AS i ON p.HMY = i.HMY
END
END
If have run profiler to see if I can see what the trigger is trying to do and it doesn’t show anything related to the trigger. I did cause the trigger that is working to fire and it didn’t show anything in the profiler either.
Any ideas?
July 3, 2008 at 12:32 pm
Change to ... ????
CREATE TRIGGER trUpdateBeginVacant
ON propbut_mi
AFTER INSERT, UPDATE
AS
...
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 3, 2008 at 12:45 pm
Try to segregate IF (UPDATE(DDELIVERED) OR UPDATE(DMOVEIN) OR UPDATE(DMOVEOUT)) to 3 IF conditions , as i have same kind of triggers in my environment where i use different IF conditions , may be it help you.
July 3, 2008 at 12:45 pm
I used FOR since that was how the examples were written. Plus the documentation says, "AFTER is the default when FOR is the only keyword specified."
It seems like it would be the same thing whether I use FOR or AFTER or is there something subtle I'm missing on this one?
July 3, 2008 at 12:47 pm
nary (7/3/2008)
Try to segregate IF (UPDATE(DDELIVERED) OR UPDATE(DMOVEIN) OR UPDATE(DMOVEOUT)) to 3 IF conditions , as i have same kind of triggers in my environment where i use different IF conditions , may be it help you.
I tried that already thinking that may have been the problem. In fact, just for testing purposes, I only had it test for UPDATE(DMOVEOUT) and only updated that column. Still didn't fire.
July 3, 2008 at 12:49 pm
jim.powers (7/3/2008)
I used FOR since that was how the examples were written. Plus the documentation says, "AFTER is the default when FOR is the only keyword specified."It seems like it would be the same thing whether I use FOR or AFTER or is there something subtle I'm missing on this one?
DUH! I must be ready for the weekend and forgot that.
Anyway, are you intending on updateing all records with the same HCODE (multiple records) or only the single record updated or inserted. If the latter, then you need to refer to the HMY in the trigger.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 3, 2008 at 1:00 pm
Jason Selburg (7/3/2008)
Anyway, are you intending on updating all records with the same HCODE (multiple records) or only the single record updated or inserted. If the latter, then you need to refer to the HMY in the trigger.
We have to update all of the records with the same HCODE for all future months. The HCODE is the property code for that location. The sample data is accurately representative of the number of records for the coming budget year. Our property managers will be entering the forecasted Delivered units (DDELIVERED), forecasted Units moved into (DMOVEIN), and forecasted Units moved out of (DMOVEOUT). Actually, they will enter the other items as well but these three are the only ones I care about at this point.
What is supposed to happen is: if, during the month of February, we have 10 move outs (and enter that into the application), the trigger needs to add 10 to the DBEGVACANT field for all future months beginning with March through December. Of course, additional math will be there for the other two fields.
July 3, 2008 at 1:18 pm
I'm still not sure why yours isn't working, but the code below seems to get the correct results. 😀
ALTER TRIGGER [trUpdateBeginVacant]
ON [dbo].[propbut_mi]
FOR INSERT, UPDATE
AS
IF (UPDATE(DDELIVERED) OR UPDATE(DMOVEIN) OR UPDATE(DMOVEOUT))
BEGIN
UPDATE p
SET DBEGVACANT
= (ISNULL(i.DBEGVACANT, 0)
+ ISNULL(i.DDELIVERED, 0)
+ ISNULL(i.DMOVEOUT, 0)
- ISNULL(i.DMOVEIN, 0))
FROM propbut_mi AS p
INNER JOIN inserted AS i
ON i.HCODE = p.HCODE AND LEFT(p.SMONTH,2) > LEFT(i.SMONTH,2)
END
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 3, 2008 at 1:25 pm
Jim - that trigger is working. It's just not doing what you want of it.... you're looking for some kind of runing total, and that syntax ain't going to accomplish it....
I'm definitely getting rows changing when I trigger the update, based on what you have.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 3, 2008 at 1:35 pm
Well, that doesn't work either. Is there possibly an issue with having two triggers on the same table that may be causing the issue?
Is there a way to see what SQL Server is trying to do with this trigger when the application updates the table?
July 3, 2008 at 1:49 pm
SQL Profiler will capture the info if you use the stored proc sp* events. If will show you what is being fired....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 3, 2008 at 1:57 pm
OK, that's a step in the right direction. I can see now that it is firing. Can I get it record what the values are for the variables? All it is showing is the raw statement. Maybe just another column in the profiler?
July 3, 2008 at 2:04 pm
Here's something, could the working trigger be interfering with the trigger that isn't working? Have you tried disabling (or deleting) the working one to see if the one you are working on is working correctly?
Is there a specific order in which the triggers should fire? You can specify which one fires FIRST and which fires LAST. Beyond that, you don't have much control.
😎
July 4, 2008 at 1:05 pm
Try removing the IF conditions and see whether trigger are firing or not and after that add one by one.
Thanks -- Vj
July 4, 2008 at 9:46 pm
The trigger fires just fine... the UPDATE is the problem... or rather the data, data order, and criteria for the update is the problem... This never qualifies with the data and the order of the data inserts that you have in your original post...
AND LEFT(SMONTH,2) > @month
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply