Results 1 to 13 of 13

Thread: Update trigger

  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    Unanswered: Update trigger

    I have this table:
    Code:
    CREATE TABLE [dbo].[EB_Eprom] (
    	[EpromID] [int] IDENTITY (1, 1) NOT NULL ,
    	[Naam_Spel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Versie] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Checksum1] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Checksum2] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Omschrijving] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Datum_vrijgave] [smalldatetime] NULL ,
    	[Kabinet] [int] NULL ,
    	[Merk] [int] NULL ,
    	[Wet] [int] NULL ,
    	[Bestand_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Bestand_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Lastedit] [datetime] NULL 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    and i created this trigger:

    Code:
    CREATE trigger CT_EB_EpromBestanden on dbo.EB_Eprom
    after update
    as
    update EB_Eprom 
     set 	eb_eprom.bestand_1 = '../data/'+str(b.bestand_1)  ,
                 eb_eprom.bestand_2 = '../data/'+str(b.bestand_2)
    from EB_Eprom a join inserted b
    on a.EpromID= b.EpromID
    the thing that this trigger must do is update the given value with the custom path ../data/

    Can someone tell me what i do wrong!!
    Thanx already
    Cheerz Wimmo
    Last edited by Wimmo; 03-15-04 at 10:03.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why are you doing a join...

    I don't think (damn I hate when that happens) that the row is not yet in the table...so there can't be a join between inserted and your table...


    Gotta test to make sure...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Originally posted by Brett Kaiser
    Why are you doing a join...

    I don't think (damn I hate when that happens) that the row is not yet in the table...so there can't be a join between inserted and your table...


    Gotta test to make sure...
    I asked some other guy for another trigger and he gives this answer: http://www.dbforums.com/showthread.p...ighlight=wimmo
    and then build this trigger with the other as example!!
    I am kindy newbie

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    My bad...

    I thought INSERT for some reason...

    Try this..

    Code:
    CREATE TRIGGER CT_EB_EpromBestanden ON dbo.EB_Eprom
    AFTER UPDATE 
    AS
         UPDATE a 
               SET  bestand_1 = '../data/'+str(b.bestand_1)
    	     , bestand_2 = '../data/'+str(b.bestand_2)
              FROM EB_Eprom a 
     INNER JOIN inserted b
                 ON a.EpromID= b.EpromID
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Originally posted by Brett Kaiser
    My bad...

    I thought INSERT for some reason...

    Try this..

    Code:
    CREATE TRIGGER CT_EB_EpromBestanden ON dbo.EB_Eprom
    AFTER UPDATE 
    AS
         UPDATE a 
               SET  bestand_1 = '../data/'+str(b.bestand_1)
    	     , bestand_2 = '../data/'+str(b.bestand_2)
              FROM EB_Eprom a 
     INNER JOIN inserted b
                 ON a.EpromID= b.EpromID

    Getting error converting varchar to float

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Take out the str function...you don't need it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Originally posted by Brett Kaiser
    Take out the str function...you don't need it...
    I removed the str but now i get this error:

    string or binary data would be truncated and
    the statement has been terminated

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, Try adding RTRIM()
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Originally posted by Brett Kaiser
    OK, Try adding RTRIM()
    Where do i place this rtrim?

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This works...but for some reason I get 8 rows affected...makes no sense..

    Code:
    USE Northwind
    GO
    
    CREATE TABLE [dbo].[EB_Eprom] (
    	[EpromID] [int] IDENTITY (1, 1) NOT NULL ,
    	[Naam_Spel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Versie] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Checksum1] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Checksum2] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Omschrijving] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Datum_vrijgave] [smalldatetime] NULL ,
    	[Kabinet] [int] NULL ,
    	[Merk] [int] NULL ,
    	[Wet] [int] NULL ,
    	[Bestand_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Bestand_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Lastedit] [datetime] NULL 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    CREATE trigger CT_EB_EpromBestanden on dbo.EB_Eprom
    AFTER UPDATE
    AS
    UPDATE    EB_Eprom 
       SET 	  eb_eprom.bestand_1 = '../data/'+RTRIM(b.bestand_1)
    	, eb_eprom.bestand_2 = '../data/'+RTRIM(b.bestand_2)
      FROM	  EB_Eprom a join inserted b
        ON	  a.EpromID= b.EpromID
    GO
    
    INSERT INTO EB_EProm(bestand_1, bestand_2)
         SELECT 'X','Y'
    GO
    
    SELECT EPRomID, bestand_1, bestand_2
      FROM EB_EProm
    GO
    
    UPDATE EB_EProm
       SET bestand_1 = 'A'
         , bestand_2 = 'B'
     WHERE EPromID = 1
    GO
    
    SELECT EPRomID, bestand_1, bestand_2
      FROM EB_EProm
    GO
    
    -- Clean up my Environment
    -- DROP TABLE EB_EProm
    GO
    I'm screwing up somewheere...I'll have to check...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This is REALLY bugging me...I can't see it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Originally posted by Brett Kaiser
    This is REALLY bugging me...I can't see it...
    I am already really thankful! I am goinmg to try this and let you know what it did!

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Wimmo
    I am already really thankful! I am goinmg to try this and let you know what it did!
    No....

    Not You...

    Why the damn thing is showing me 8 rows affected!

    Doesn't make sense...

    It should be

    1 Row Affected

    1 Row Affected

    Like that


    Noy 8

    Oh, and Put SET NOCOUNT ON at the top of the TRIGGER

    and SET NOCOUNT OFF on the way out...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •