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