CODEWITHSUNDEEP
sqlsql-servertriggersssrs-2012
user10196069
user10196069

Reputation:

SSRS Trigger to update Description with additional information

I'm trying to create a trigger that will update Description on SSRS for the record that has been edited.

I am trying to add schedule information into the Description column on SSRS on screen where you assign schedules to a report.

I have the below query:

CREATE TRIGGER UpdateDescription
ON [dbo].[Subscriptions]
AFTER INSERT,  UPDATE
AS
UPDATE dbo.Subscriptions
SET [Description] = a.Description + ' | Schedule: ' + d.Name
FROM dbo.Subscriptions a
LEFT JOIN dbo.Catalog b
ON a.Report_OID = b.ItemID
LEFT JOIN dbo.ReportSchedule c
ON b.ItemID = c.ReportID
LEFT JOIN dbo.Schedule d
ON c.ScheduleID = d.ScheduleID
WHERE

When that trigger is in place and I edit a report it changes the description for every schedule for all reports.... What sort of parameter could I add to the 'WHERE' statement at the end so it only edits the description for the schedule I am currently creating/updating?

Upvotes: 0

Views: 66

Answers (2)

otri
otri

Reputation: 580

I think you need to join onto INSERTED table, which holds all the records modified

INNER JOIN INSERTED as I ON dbo.Subscriptions.Id = I.Id

Upvotes: 1

Dave.Gugg
Dave.Gugg

Reputation: 6771

Rather than use a WHERE clause, you should INNER JOIN to the INSERTED table. This will only contain rows that were inserted into the table:

CREATE TRIGGER UpdateDescription
ON [dbo].[Subscriptions]
AFTER INSERT, UPDATE
AS
UPDATE dbo.Subscriptions
SET [Description] = a.Description + ' | Schedule: ' + d.Name
FROM dbo.Subscriptions a
    INNER JOIN inserted i ON i.SubscriptionID = a.SubscriptionID
    LEFT JOIN dbo.Catalog b ON a.Report_OID = b.ItemID
    LEFT JOIN dbo.ReportSchedule c ON b.ItemID = c.ReportID
    LEFT JOIN dbo.Schedule d ON c.ScheduleID = d.ScheduleID;

Upvotes: 0

Related Questions