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
1 Reply
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Gokhul VarmanPosted Aug 14, 2017, 7:40 AM