How to Create a Trigger in SQL Server to Update Cascade When @@RowCount > 1?

Posted on

Question :

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)

Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *