-- Retrieve the EwaResponse_DMTrigger(s) that currently exist for P&G and store it in a temp table for later comparison --
CREATE TABLE #ExistingEwaResponse_DMTriggers (
EwaResponseID INT,
DMTriggerID INT,
UserID INT
)
INSERT INTO #ExistingEwaResponse_DMTriggers
SELECT
EwaResponse_DMTrigger.EwaResponseID,
EwaResponse_DMTrigger.DMTriggerID,
EwaResponse.UserID
FROM
EwaResponse_DMTrigger WITH (NOLOCK)
INNER JOIN
EwaResponse WITH (NOLOCK)
ON EwaResponse.EwaResponseID = EwaResponse_DMTrigger.EwaResponseID
WHERE
EwaResponse_DMTrigger.EwaResponseID IN (
SELECT
EwaResponse.EwaResponseID
FROM
EwaResponse WITH (NOLOCK)
INNER JOIN
Users WITH (NOLOCK)
ON Users.UserID = EwaResponse.UserID
INNER JOIN
Site WITH (NOLOCK)
ON Site.SiteID = Users.SiteID
WHERE
Site.CompanyID = 474)
ORDER BY
EwaResponse_DMTrigger.EwaResponseID DESC
---------------------------------
-- Execute spGetDMForm for all P&G EwaResponse(s) --
DECLARE
@EwaResponseID INT,
@UserID INT
DECLARE
LOOPYcursor CURSOR FOR
SELECT
EwaResponse.EwaResponseID,
EwaResponse.UserID
FROM
EwaResponse WITH (NOLOCK)
INNER JOIN
Users WITH (NOLOCK)
ON Users.UserID = EwaResponse.UserID
INNER JOIN
Site WITH (NOLOCK)
ON Site.SiteID = Users.SiteID
WHERE
Site.CompanyID = 474
ORDER BY
EwaResponse.EwaResponseID DESC
OPEN LOOPYcursor
FETCH NEXT FROM LOOPYcursor INTO
@EwaResponseID,
@UserID
WHILE @@Fetch_Status = 0
BEGIN
EXECUTE [dbo].[spGetDMForm] @EwaResponseID, @UserID, null
FETCH NEXT FROM LOOPYcursor INTO
@EwaResponseID,
@UserID
END
CLOSE LOOPYcursor
DEALLOCATE LOOPYcursor
---------------------------------
-- Retrieve the EwaResponse_DMTrigger(s) that are new for P&G --
SELECT
CurrentEwaResponse_DMTrigger.EwaResponseID,
CurrentEwaResponse_DMTrigger.DMTriggerID,
CurrentEwaResponse_DMTrigger.UserID
FROM
(
SELECT
EwaResponse_DMTrigger.EwaResponseID,
EwaResponse_DMTrigger.DMTriggerID,
EwaResponse.UserID
FROM
EwaResponse_DMTrigger WITH (NOLOCK)
INNER JOIN
EwaResponse WITH (NOLOCK)
ON EwaResponse.EwaResponseID = EwaResponse_DMTrigger.EwaResponseID
) AS CurrentEwaResponse_DMTrigger
WHERE
CurrentEwaResponse_DMTrigger.EwaResponseID IN
(
SELECT
EwaResponse.EwaResponseID
FROM
EwaResponse WITH (NOLOCK)
INNER JOIN
Users WITH (NOLOCK)
ON Users.UserID = EwaResponse.UserID
INNER JOIN
Site WITH (NOLOCK)
ON Site.SiteID = Users.SiteID
WHERE
Site.CompanyID = 474
)
AND NOT EXISTS
(
SELECT
EwaResponseID,
DMTriggerID,
UserID
FROM
#ExistingEwaResponse_DMTriggers
WHERE
#ExistingEwaResponse_DMTriggers.EwaResponseID =
CurrentEwaResponse_DMTrigger.EwaResponseID
AND #ExistingEwaResponse_DMTriggers.DMTriggerID =
CurrentEwaResponse_DMTrigger.DMTriggerID
AND #ExistingEwaResponse_DMTriggers.UserID =
CurrentEwaResponse_DMTrigger.UserID
)
ORDER BY
CurrentEwaResponse_DMTrigger.EwaResponseID DESC
---------------------------------
-- Drop the temp table created --
DROP TABLE #ExistingEwaResponse_DMTriggers
---------------------------------
