My update does not fire a trigger 1

[foxbox]

I created a simple ON Update trigger on MyTable.
When i open MyTable and change a field, the trigger works fine (basically it is make a copy of the altered row in another table).
But when i do an UPDATE MyTable SET ... the trigger is NOT working. In some situations that may be good behavior, but in this case i want the trigger to run on every row in MyTable.

I'm working with MS SQL 2005. I tried ENABLE TRIGGER, without succes.

 

[bborissov]

Can you post the TRIGGER code?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[foxbox]

ALTER TRIGGER [dbo].[MyTrigger] ON [dbo].[MyTable]  AFTER INSERT,UPDATE
AS 
BEGIN

DECLARE @AFSPRAAKTYPE TINYINT
DECLARE @ADRESID INT
DECLARE @VOLGNR INT
DECLARE @PLANDATUM datetime
DECLARE @BEGINTIJD varchar(50)
DECLARE @STARTDATUM varchar(50)
DECLARE @EINDDATUM varchar(50)
DECLARE @EINDTIJD varchar(50)

SELECT @ADRESID = adresid, @VOLGNR = volgnr, @AFSPRAAKTYPE = [type], 
       @PLANDATUM = plandatum, @BEGINTIJD = begintijd, @EINDTIJD = eindtijd  FROM inserted


IF @AFSPRAAKTYPE in (1, 2 ,4) 
 BEGIN

 IF @PLANDATUM > convert(datetime,'28-03-2010',105) AND @PLANDATUM < convert(datetime,'01-11-2010',105) 
  BEGIN 
   IF  rtrim(@BEGINTIJD) = '' 
    SET @STARTDATUM = convert(char(10),@PLANDATUM ,120) + 'T07:00:00.000Z'
   ELSE 
    SET @STARTDATUM = convert(char(10),@PLANDATUM ,120) + 'T' +   RIGHT(100 + LEFT(@BEGINTIJD, 2) - 2, 2)  + ':' + right(@BEGINTIJD,2) + ':00.000Z'
  END
 ELSE
  BEGIN
    IF rtrim(@BEGINTIJD) = '' 
     SET @STARTDATUM = convert(char(10),@PLANDATUM ,120) + 'T08:00:00.000Z'
    ELSE 
     SET @STARTDATUM = convert(char(10),@PLANDATUM ,120) + 'T' +   RIGHT(100 + LEFT(@BEGINTIJD, 2) - 1, 2)  + ':' + right(@BEGINTIJD,2) + ':00.000Z'
  END
 

 IF @PLANDATUM > convert(datetime,'28-03-2010',105) AND @PLANDATUM < convert(datetime,'01-11-2010',105) 
  BEGIN
   IF (rtrim(@BEGINTIJD) = '') 
   SET @EINDDATUM=  convert(char(10),@PLANDATUM,120) + 'T07:00:00.000Z'
   ELSE
   BEGIN
    IF (rtrim(@EINDTIJD) = '') 
    SET @EINDDATUM =  convert(char(10),@PLANDATUM,120) + 'T' +  RIGHT(100 + LEFT(@BEGINTIJD, 2) - 2, 2)  + ':' + right(@BEGINTIJD,2)  + ':00.000Z'
    ELSE
    SET @EINDDATUM= convert(char(10),@PLANDATUM,120) + 'T' +  RIGHT(100 + LEFT(@EINDTIJD, 2) - 2, 2)  + ':' + right(@EINDTIJD,2)  + ':00.000Z'
   END
  END
 ELSE
 BEGIN
 IF (rtrim(@BEGINTIJD) = '') 
 SET @EINDDATUM=  convert(char(10),@PLANDATUM,120) + 'T08:00:00.000Z'
 ELSE
  BEGIN 
  IF (rtrim(@EINDTIJD) = '') 
  SET @EINDDATUM =  convert(char(10),@PLANDATUM,120) + 'T' +  RIGHT(100 + LEFT(@BEGINTIJD, 2) - 1, 2)  + ':' + right(@BEGINTIJD,2)  + ':00.000Z'
  ELSE
  SET @EINDDATUM= convert(char(10),@PLANDATUM,120) + 'T' +  RIGHT(100 + LEFT(@EINDTIJD, 2) - 1, 2)  + ':' + right(@EINDTIJD,2)  + ':00.000Z'
  END
 END 

 INSERT INTO MyTable2 (activiteitid,begintijd,eindtijd)
  SELECT a.recnum,@STARTDATUM,@EINDDATUM
  FROM MyTable A
  WHERE A.adresid = @ADRESID AND A.volgnr = @VOLGNR
 END
END

 

[foxbox]

just to be clear: a single update (in the VS Grid) works A-oke.
My program update: A-oke.

But when i do eg an
UPDATE MyTable SET lastchange = getdate()

iwant the trigger run on every changed record..

 

[gmmastros]

That's not the way triggers work. The trigger fires once for the whole batch. There is no way to change this behavior. Instead, you should write your trigger so that it can handle multiple rows updated within the same query.

I don't have time right now to look at your code and make suggestions. Sorry. If at all possible, do not use a cursor or while loop since this will slow down the execution a lot.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[foxbox]

oke, so i wrote a q&d sp with a cursor. it does a non-sense update (devide a numeric field by 1), but it does what i want: force the trigger to be fired for every row.

CREATE PROCEDURE ForceTrigger 
AS
BEGIN
	SET NOCOUNT ON;

DECLARE @recnum numeric;
DECLARE db_cursor CURSOR FOR  
SELECT     RECNUM
 FROM         MyTable
 WHERE     (PLANDATUM > GETDATE())

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @recnum   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       UPDATE MyTable SET [type] = [type]/1 WHERE recnum = @recnum  
     
       FETCH NEXT FROM db_cursor INTO @recnum   
END   

END
GO

 

[gmmastros]

I think you are making a mistake. The cursor in the calling procedure will cause the whole thing to be slower. Besides, when you write a trigger, it should be because you want this code to ALWAYS execute whenever data is updated. If you want the trigger code to fire just for a single procedure, then take the code out of the trigger and put it in the procedure. If you want the code to fire any time data is updated, then you should have the code in a trigger, but write it to accomodate multiple rows.

One common mistake programmers make is thinking they have handled all things. I'm here to tell you that you have a hole in your logic. Even if you don't have a problem now, you cannot be sure that you (or another programmer) won't write code that updates multiple rows in the future. Will you remember this trigger 6 months from now?

I know it's a pain, but I encorage you to fix the problem now. You'll be glad you did.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[foxbox]

the trigger works fine. i only update the table via ASP programs. but the database is already filled with records, and needed a ONE TIME ONLY run for every row in the table.
If i change the trigger logic in the future, i may need to do that again.