Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I Have 2 Table

Table1
 | column name | type        |
 |-------------+-------------|
 | ID1         | Int         |
 | Name        | NVARCHAR(50)|


Table2
 | column name | type        |
 |-------------+-------------|
 | ID1         | Int         |
 | ID          | Int         |
 | Name        | NVARCHAR(50)|

I want to create a relation in Table1.ID1 And Table2.ID1 with TRIGGER For Cascade UPDATE that support the bellow T-Sql:

Update Table1 Set ID1 = ID1 + 1

How To create a trigger to cascade on update where the updated row count is more than 1?

(@@RowCount > 1)

share|improve this question
    
Update when what happens? And which rows? –  ypercube Jul 1 '12 at 19:17
    
update must be increase ID1! –  Amir Jul 1 '12 at 19:51
    
i want to create a trigger as after update, For All Rows, or for any rows that was changed –  Amir Jul 1 '12 at 20:42
    
Your question is not clear, however, it is usually a bad idea to update a primary key. I guess you have a conceptual problem in your table design. Give a detailed description with a case where such an update is required. –  Emmad Kareem Jul 2 '12 at 9:57

1 Answer 1

I Found it! this is available with use CURSOR! I write a code that slove the problem:

CREATE TRIGGER [dbo].[EditUserUNameTrigger]
   ON  [dbo].[User]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF(NOT UPDATE(UName))
        RETURN

    DECLARE @Old NVARCHAR(250)
    DECLARE @New NVARCHAR(250)
    DECLARE @DEL CURSOR
    DECLARE @INS CURSOR

    SET @DEL = CURSOR FOR SELECT UName FROM DELETED
    SET @INS = CURSOR FOR SELECT UName FROM INSERTED 

    OPEN @DEL
    OPEN @INS

    WHILE 1 = 1
    BEGIN
        FETCH NEXT FROM @DEL INTO @Old
        FETCH NEXT FROM @INS INTO @New

        IF(@@FETCH_STATUS <> 0)
            BREAK

        UPDATE [DesktopLayout] SET UName = @New WHERE [DesktopLayout].UName = @Old 
    END

    CLOSE @DEL
    CLOSE @INS

    DEALLOCATE @DEL
    DEALLOCATE @INS
END
share|improve this answer
    
This may generate duplicate keys. –  Emmad Kareem Jul 30 '12 at 0:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.