Substitute of trigger in a merge statement.
-
Hello Everyone
I hope you all are rocking.
My question is regarding the merge statement. I am using Microsoft SQL Azure (RTM) - 12.0.2000.8.
I am transferring users' data from one table to another table with some manipulation of data.
Everything works fine without any issues.
Now I have another table that maps the user id with the module id so that users can access the module.
So for that, I put a trigger on the table if a new record is inserted then a new record entry should go into the module table with the user id.
But the merge statement takes all transactions as a single transaction so always the one user id inserted with the module id.
It means the trigger won't work in this case.
So my question is; is it the correct way / feasible to write the SQL code just after the new user is inserted in the table?
Because there is a bulk data process using merge.
For reference here is my merge statement and trigger code which I used.
--Merge Store Procedure
ALTER PROC [dbo].[usp_transfer_on_premise_data_to_cp_database_v2] (
@btStatus BIT = 0,
@vcFirstName VARCHAR(100) = NULL,
@vcLastName VARCHAR(100) = NULL,
@vcEmail VARCHAR(300) = NULL,
@vcMobile VARCHAR(100) = NULL,
@vcUserName VARCHAR(50) = NULL,
@vcprojectnumber NVARCHAR(50) = NULL,
@SAPno NVARCHAR(50) = NULL,
@supervisor BIGINT = 0,
@dateofjoining DATETIME = NULL,
@dateofend DATETIME = NULL,
@_joinyear FLOAT = 0,
@_endyear FLOAT = 0,
@_joinminus FLOAT = 0,
@_endminus FLOAT = 0,
@datemodi DATETIME = NULL,
@CountryID INT = 221,
@telephoneno NVARCHAR(50) = NULL,
@salutation VARCHAR(50) = NULL,
@Gender NVARCHAR(50) = NULL,
@carryyear BIGINT = 2021,
@carryloseyear BIGINT = 2021,
@emptype NVARCHAR(50) = NULL,
@ContractPercentage DECIMAL(18, 2) = NULL,
@CostCenter NVARCHAR(50) = NULL,
@CostCenter1 NVARCHAR(50) = NULL,
@CostCenter2 NVARCHAR(50) = NULL,
@CostCenter3 NVARCHAR(50) = NULL,
@CostCenter4 NVARCHAR(50) = NULL,
@CostCenter5 NVARCHAR(50) = NULL
)
AS
BEGIN TRANSACTION;
MERGE AdminMaster_GEN AS HSO
USING HR_SAP AS HS
ON TRY_CAST(HSO.SAPno AS BIGINT) = CAST(HS.SAP_No AS BIGINT)
-- INSERT NEW RECORDS--
WHEN NOT MATCHED BY TARGET
THEN
INSERT (
teamid,
vcFirstName,
vcLastName,
vcEmail,
vcMobile,
vcUserName,
vcprojectnumber,
SAPno,
dateofjoining,
dateofend,
_joinyear,
_endyear,
_joinminus,
_endminus,
CountryID,
telephoneno,
salutation,
Gender,
emptype,
chAdminType,
ContractPercentage,
CostCenter1,
CostCenter2,
CostCenter3,
CostCenter4,
CostCenter5,
sync_on
)
VALUES (
CASE
WHEN HS.designation = 'NMA'
THEN 27
WHEN HS.designation = 'AMA'
THEN 22
WHEN HS.designation = 'IMA'
THEN 30
ELSE 21
END,
HS.first_name,
HS.last_name,
HS.email,
HS.mobile_no,
SUBSTRING(HS.email, 0, CHARINDEX('@', HS.email)),
-- HS.project_no,
(
SELECT CASE
WHEN HS.project_no <> ''
THEN SUBSTRING(HS.project_no, 2, LEN(HS.project_no) - 11) + '.' + SUBSTRING(HS.project_no, 4, LEN(HS.project_no) - 9) + '.' + SUBSTRING(HS.project_no, 8, LEN(HS.project_no) - 12) + '-' + SUBSTRING(HS.project_no, 9, LEN(HS.project_no) - 10) + '.' + SUBSTRING(HS.project_no, 12, LEN(HS.project_no) - 11)
ELSE CAST(CAST(HS.cost_center1 AS BIGINT) AS NVARCHAR(50))
END
), -- select * from HR_SAP),
CAST(HS.sap_no AS BIGINT),
CASE
WHEN HS.designation = 'NMA'
THEN CAST(HS.date_of_joining AS DATE)
ELSE CAST(HS.AMA_joiningDate AS DATE)
END,
CAST(HS.date_of_leaving AS DATE),
DATEPART(yy, CASE
WHEN HS.designation = 'NMA'
THEN CAST(HS.date_of_joining AS DATE)
ELSE CAST(HS.AMA_joiningDate AS DATE)
END),
DATEPART(yy, HS.date_of_leaving),
ISNULL(dbo.CalculateLeaveMinus(HS.country, HS.designation, CASE
WHEN HS.designation = 'NMA'
THEN CAST(HS.date_of_joining AS DATE)
ELSE CAST(HS.AMA_joiningDate AS DATE)
END, CAST(HS.date_of_leaving AS DATE), 1), 0), --_joinminus
ISNULL(dbo.CalculateLeaveMinus(HS.country, HS.designation, CASE
WHEN HS.designation = 'NMA'
THEN CAST(HS.date_of_joining AS DATE)
ELSE CAST(HS.AMA_joiningDate AS DATE)
END, CAST(HS.date_of_leaving AS DATE), 2), 0), --_endminus
ISNULL((
SELECT bintid_pk
FROM country_master
WHERE country_code = HS.countrycode
), 777),
HS.telephone_no,
HS.salutation,
UPPER(LEFT(HS.gender, 1)) + + LOWER(SUBSTRING(HS.gender, 2, LEN(HS.gender))),
CASE
WHEN HS.designation = 'EH'
THEN 'DW'
ELSE 'NP'
END,
'v',
HS.contract_percentage,
HS.cost_center1,
HS.cost_center2,
HS.cost_center3,
HS.cost_center4,
HS.cost_center5,
GETDATE()
)
--START : UPDATE EXISTING RECORDS--
WHEN MATCHED
THEN
UPDATE
SET HSO.btStatus = 1,
HSO.teamid = CASE
WHEN HS.designation = 'NMA'
THEN 27
WHEN HS.designation = 'AMA'
THEN 22
WHEN HS.designation = 'IMA'
THEN 30
ELSE 21
END,
HSO.vcFirstName = HS.first_name,
HSO.vcLastName = HS.last_name,
HSO.vcEmail = HS.email, --EMAIL AND SAP NO UNCOMMENT AFTER DISCUSSION
HSO.vcMobile = CASE WHEN HS.mobile_no<>''
THEN HS.mobile_no else HSO.vcMobile END,
HSO.CostCenter1 = HS.cost_center1,
HSO.CostCenter2 = HS.cost_center2,
HSO.CostCenter3 = HS.cost_center3,
HSO.CostCenter4 = HS.cost_center4,
HSO.CostCenter5 = HS.cost_center5,
HSO.gender= UPPER(LEFT(HS.gender, 1)) + + LOWER(SUBSTRING(HS.gender, 2, LEN(HS.gender))),
HSO.ContractPercentage = HS.contract_percentage,
HSO.sync_on = GETDATE(),
HSO.vcUserName = SUBSTRING(HS.email, 0, CHARINDEX('@', HS.email)),
HSO.vcprojectnumber = (
SELECT CASE
WHEN HS.project_no <> ''
THEN SUBSTRING(HS.project_no, 2, LEN(HS.project_no) - 11) + '.' + SUBSTRING(HS.project_no, 4, LEN(HS.project_no) - 9) + '.' + SUBSTRING(HS.project_no, 8, LEN(HS.project_no) - 12) + '-' + SUBSTRING(HS.project_no, 9, LEN(HS.project_no) - 10) + '.' + SUBSTRING(HS.project_no, 12, LEN(HS.project_no) - 11)
ELSE CAST(CAST(HS.cost_center1 AS BIGINT) AS NVARCHAR(50))
END
), --UPDATE WITH DOT
--HSO.SAPno = CAST(HS.sap_no AS BIGINT),
HSO.dateofjoining = CASE
WHEN HS.designation = 'NMA'
THEN CAST(HS.date_of_joining AS DATE)
ELSE CAST(HS.AMA_joiningDate AS DATE)
END,
HSO.dateofend = CAST(HS.date_of_leaving AS DATE),
HSO._joinyear = DATEPART(yy, CASE
WHEN HS.designation = 'NMA'
THEN CAST(HS.date_of_joining AS DATE)
ELSE CAST(HS.AMA_joiningDate AS DATE)
END),
HSO._endyear = DATEPART(yy, HS.date_of_leaving),
HSO._joinminus = ISNULL(dbo.CalculateLeaveMinus(HS.country, HS.designation, CASE
WHEN HS.designation = 'NMA'
THEN CAST(HS.date_of_joining AS DATE)
ELSE CAST(HS.AMA_joiningDate AS DATE)
END, CAST(HS.date_of_leaving AS DATE), 1), 0), --_joinminus
HSO._endminus = ISNULL(dbo.CalculateLeaveMinus(HS.country, HS.designation, CASE
WHEN HS.designation = 'NMA'
THEN CAST(HS.date_of_joining AS DATE)
ELSE CAST(HS.AMA_joiningDate AS DATE)
END, CAST(HS.date_of_leaving AS DATE), 2), 0), --_endminus
--HSO.datemodi = GETDATE(),
HSO.CountryID = ISNULL((
SELECT bintid_pk
FROM country_master
WHERE country_code = HS.countrycode
), 777),
HSO.telephoneno =CASE WHEN HS.telephone_no<>''
THEN HS.telephone_no else HSO.telephoneno END,
HSO.salutation = HS.salutation,
HSO.emptype = CASE
WHEN HS.designation = 'EH'
THEN 'DW'
ELSE 'NP'
END
--END : UPDATE EXISTING RECORDS--
WHEN NOT MATCHED BY SOURCE AND HSO.chAdminType<>'s'
--IN CASE JSON DOES NOT CONTAINS THE RECORD THEN DEACTIVATE THE USER
THEN
UPDATE
SET HSO.btStatus = 0;
--START : UPDATE SUPERVISOR AFTER ALL DATA TRANSFER TO THE TABLE--
UPDATE HSO
SET HSO.supervisor = ISNULL(O2.[bintId_Pk], 0)
FROM [AdminMaster_GEN] AS HSO
LEFT JOIN [HR_SAP] AS H ON TRY_CAST(HSO.[SAPno] AS BIGINT) = CAST(H.[SAP_no] AS BIGINT)
LEFT JOIN [AdminMaster_GEN] AS O2 ON TRY_CAST(O2.[SAPno] AS BIGINT) = CAST(H.[supervisor_sap_no] AS BIGINT);
--END : UPDATE SUPERVISOR AFTER ALL DATA TRANSFER TO THE TABLE--
IF @@ERROR <> 0
BEGIN
--SET @pintCheck = 2
ROLLBACK TRAN;
INSERT INTO ExceptionLog (
ErrorLine,
ErrorMessage,
ErrorNumber,
ErrorProcedure,
ErrorSeverity,
ErrorState,
DateErrorRaised
)
VALUES (
Error_Line(),
Error_Message(),
Error_Number(),
Error_Procedure(),
Error_Severity(),
Error_State(),
GETDATE()
)
END;
ELSE
BEGIN
--SET @pintCheck = 0
COMMIT TRAN;
END-- Trigger to assign modules to new users
ALTER TRIGGER [dbo].[tr_update_rights] ON [dbo].[AdminMaster_GEN]
AFTER INSERT,
UPDATE
AS
BEGIN
DECLARE @team_old BIGINT;
DECLARE @team_new BIGINT;
DECLARE @emp_id BIGINT;
SELECT @emp_id = bintId_Pk
FROM inserted;
SELECT @team_old = teamid
FROM deleted;
SELECT @team_new = teamid
FROM inserted;
SET NOCOUNT ON;
DECLARE @insert AS INT
SET @insert = CASE
WHEN EXISTS (
SELECT *
FROM deleted
)
THEN 0
ELSE 1
END
IF (@team_old <> @team_new)
OR (@insert = 1)
BEGIN
DELETE
FROM AdminSectionPermission_GEN
WHERE bIntAdminId_FK = @emp_id;
INSERT INTO AdminSectionPermission_GEN (
[bIntAdminId_FK],
[bIntSectionId_FK],
[create],
[edit],
[view],
[delete],
[export],
[process],
[approve],
[updatedOn]
)
SELECT @emp_id,
module_id,
MAX(CAST([create] AS INT)) AS [create],
MAX(CAST([edit] AS INT)) AS [edit],
MAX(CAST([view] AS INT)) AS [view],
MAX(CAST([delete] AS INT)) AS [delete],
MAX(CAST([export] AS INT)) AS [export],
MAX(CAST([process] AS INT)) AS [process],
MAX(CAST([approve] AS INT)) AS [approve],
GETDATE()
FROM (
SELECT GR.module_id,
GR.[create],
GR.[edit],
GR.[view],
GR.[delete],
GR.[export],
GR.[process],
GR.[approve]
FROM tblGlobalRights GR
RIGHT JOIN (
SELECT EmployeeId,
RoleId
FROM tblMultipleRoleAssign
WHERE EmployeeId = @emp_id
UNION
(
SELECT bintId_Pk AS EmployeeId,
teamid AS RoleId
FROM AdminMaster_GEN
WHERE bintId_Pk = @emp_id
)
) AllRoles ON GR.role_id = AllRoles.RoleId WHERE GR.module_id IS NOT NULL
) AS table_merge
GROUP BY module_id
END
ENDKindly suggest.
-
To get a reasonable answer you are going to have to post DDL, consumable test data and the expected result.
>> But the merge statement takes all transactions as a single transaction
I think you need to read this:
https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-to-use-merge/
SELECT @team_old = teamid
FROM deleted;
SELECT @team_new = teamid
FROM inserted;Triggers should always to written to work with multiple rows. This will only work with one. Even worse it will not throw an exception if there is more than one row.
Do you really need a trigger here or can everything be done in the SP?
- Noel Kennedy wrote:
Do you really need a trigger here or can everything be done in the SP?
That I want to know.
Only you can say what else is likely to interact with your tables.
-
Only you can say what else is likely to interact with your tables.
I want all newly inserted new ids in the AdminMaster_GEN tables and then insert those ids in AdminSectionPermission_GEN with some module id.
-
You need to rewrite the trigger, get rid of the variables and make it set-based!
What you have here is a SOROW (pronounced Sorrow), Single Only Row trigger, it can only handle one transaction at the time, the problem with multi-row transactions is that you'll never know which one
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply