Atul Patil

Atul Patil

  • NA
  • 340
  • 9.1k

How to migrate MS SQL trigger into postgresql?

Aug 14 2017 7:28 AM
Please help me to migrate ms sql trigger into postgresql trigger.
 
ALTER TRIGGER [dbo].[CreateUserWiseWidgetTrigger]
ON [dbo].[Users]
AFTER INSERT
AS
BEGIN
DECLARE @WidgetId INT;
DECLARE @UserId INT
DECLARE @RoleId INT
DECLARE @TempId INT
DECLARE @X INT;
DECLARE @Y INT
DECLARE @Height INT
DECLARE @Width INT
SET @UserId = 0
SELECT @UserId = I.UserId , @RoleId=I.UserRole
FROM INSERTED I
DECLARE thecursor CURSOR FOR
SELECT [dbo].[TemplatewiseWidget].WidgetId,[dbo].[TemplatewiseWidget].TempId ,Widget.X,Widget.Y,Widget.Height,Widget.Width
FROM [dbo].[TemplatewiseWidget]
INNER JOIN [dbo].[RoleWiseTemplate]
ON [dbo].[TemplatewiseWidget].TempId=[dbo].[RoleWiseTemplate].TempId
INNER JOIN Widget
ON [dbo].[TemplatewiseWidget].WidgetId=Widget.WidgetId
WHERE [dbo].[RoleWiseTemplate].RoleId=@RoleId
AND [dbo].[RoleWiseTemplate].IsActive=1
and [dbo].[TemplatewiseWidget].IsActive=1
OPEN thecursor
FETCH NEXT FROM thecursor INTO @WidgetId,@TempId,@X,@Y,@Height,@Width
WHILE @@Fetch_status = 0
BEGIN
IF (SELECT COUNT(*) FROM [dbo].[UserWidgetTransaction] WHERE WidgetId=@WidgetId and UserId=@UserId) =0
BEGIN
INSERT INTO [dbo].[UserWidgetTransaction]
(
[UserId],
WidgetId,
RoleId,
TempId,
X,
Y,
Height,
Width,
[IsActive],
IsDeleted
)
VALUES
(
@UserId,
@WidgetId,
@RoleId,
@TempId,
@X,
@Y,
@Height,
@Width,
0,
0
)
END
ELSE
IF not exists(SELECT * FROM [dbo].[UserWidgetTransaction] WHERE WidgetId=@WidgetId and UserId=@UserId)
BEGIN
INSERT INTO [dbo].[UserWidgetTransaction]
(
[UserId],
WidgetId,
RoleId,
TempId,
X,
Y,
Height,
Width,
[IsActive],
IsDeleted
)
VALUES
(
@UserId,
@WidgetId,
@RoleId,
@TempId,
@X,
@Y,
@Height,
@Width,
0,
0
)
END
FETCH NEXT FROM thecursor INTO @WidgetId,@TempId,@X,@Y,@Height,@Width
END
CLOSE thecursor
DEALLOCATE thecursor
END
 

Answers (1)