Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    Romania
    Posts
    70

    Unanswered: Trigger + CONSTRAINT ???

    I have 2 tables

    Primary_Table (Key, ........)

    Foreign_Table (Key_1, Key_2, ............)

    I want this:

    If I update the Key in Primary_Table then
    Key_1 is Update where Key_1 = Key

    AND

    If I update the Key in Primary_Table then
    Key_2 is Update where Key_2 = Key

    I can not use CONSTRAINT because I can not use ON UPDATE CASCADE for this case (It not possible to use 2 CONSTRAINT WITH ON UPDATE CASCADE).

    I WANT TO USE TRIGGERS

    This is my trigger (but I am not sure it is the best way ?)

    CREATE TRIGGER Test ON [Primary_table]
    FOR UPDATE
    AS

    Declare @NewValue varchar(30)
    Declare @OldValue varchar(30)

    SET @NewValue = (Select Key From Inserted)
    SET @OldValue = (Select Key From Deleted)

    UPDATE [Foreign_Table]
    SET [Foreign_Table].[Key_1] = @NewValue
    FROM [Foreign_Table] WHERE [Key_1] = @OldValue

    UPDATE [Foreign_Table]
    SET [Foreign_Table].[Key_2] = @NewValue
    FROM [Foreign_Table] WHERE [Key_2] = @OldValue


    Sorry for my english.
    Please consider I am a beginner

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    CREATE TABLE myTable99(Col1 int NOT NULL, Col2 char(1)  Primary Key(Col1))
    CREATE TABLE myTable00(Col1 int NOT NULL, Col2 char(1), Col3 char(1)
    	, PRIMARY KEY (Col1, Col2)
    	, FOREIGN KEY (Col1) REFERENCES myTable99(Col1))
    GO
    
    INSERT INTO myTable99(Col1,Col2) SELECT 1,'A' UNION ALL SELECT 2, 'B'
    INSERT INTO myTable00(Col1,Col2,Col3) SELECT 1,'A','X' UNION ALL SELECT 2,'B','Y'
    GO
    
    SELECT * FROM myTable99
    SELECT * FROM myTable00
    
    -- Can't be done TRIGGER wouldn't even fire...
    UPDATE myTable99 SET Col1 = 3 WHERE Col1 = 1
    
    DROP TABLE myTable00
    GO
    
    CREATE TABLE myTable00(Col1 int NOT NULL, Col2 char(1), Col3 char(1)
    	, PRIMARY KEY (Col1, Col2)
    	, FOREIGN KEY (Col1) REFERENCES myTable99(Col1)
    	  ON UPDATE CASCADE ON DELETE CASCADE)
    GO
    
    INSERT INTO myTable00(Col1,Col2,Col3) SELECT 1,'A','X' UNION ALL SELECT 2,'B','Y'
    GO
    
    -- Now you can
    UPDATE myTable99 SET Col1 = 3 WHERE Col1 = 1
    
    SELECT * FROM myTable99
    SELECT * FROM myTable00
    
    DROP TABLE myTable00
    DROP TABLE myTable99
    GO
    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
  •