February 10, 2012 at 6:31 am
Hi
May your please review the cde below, its bit complex and unfortunately i cant do much because the database design is pooooooor and stuck with it. I only have one table that contain data and another with user desinged fields and application user.
Trigger only works when i comment out either the If update() or the other part for insertion of new records and donot run as expected.
May you please review it as see where the logis or sequesnce is going wrong.
As i mention i dont hve much choice and this is the only way i can do it.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Morena]
ON [dbo].[AMGR_User_Fields_Tbl]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @iTCTypeID9 as int
declare @iTCCodeID9 as int
declare @bNewID3 as bigint
declare @iTCTypeID3 as int
declare @iTCCodeID3 as int
declare @cClientID3 as varchar(24)
declare @cContactNumber3 as int
declare @description9 as varchar(240)
declare @Cell as varchar(24)
declare @Work as varchar(24)
declare @Fax as varchar(24)
declare @Email as varchar(24)
declare @PhisicalAddres as varchar(240)
declare @KAE as varchar(240)
--- Retrieve values been inserted
select @cClientID3 = [Client_Id], @cContactNumber3 = [Contact_Number], @iTCTypeID3 = [Type_Id], @iTCCodeID3 = [Code_Id] from inserted
-- Get codes for Primary key Account Executive names
select @iTCTypeID9 = [Type_Id], @iTCCodeID9 = [Code_Id], @description9 = Description from [AMGR_User_Field_Defs_Tbl] where [Type_Id] = '107' AND [CODE_Id] = @iTCCodeID3
-- Pull contact detail from USER Details
SELECT @Work = User_Phone_1, @Cell = User_Phone_2, @Fax = User_Phone_3, @Email = User_Email_Address ,@PhisicalAddres = user_Address_Line1 +','+ user_Address_Line2 +','+ User_City+','+ user_state_Province +','+ User_Zip_Code FROM ADMN_User_Details WHERE [User_FirstName] + ' ' + [User_Name] = @description9
----*****--------------------------------------------------------------------
INSERT ACCOUNT MANAGER NEW RECORDS - ASSIGN AM TO A LEAD
----*****-------------------------------------------------------------------
----Check if a KEA name has already been set
IF @iTCCodeID9 = @iTCCodeID3
BEGIN
If @cContactNumber3 = 0
Begin
If exists(select [Record_Id] from [AMGR_User_Fields_Tbl] where [Client_Id] = @cClientID3 and [Code_Id] = @iTCCodeID3) ---- Check if KAE name is populated
Begin
----- Insert KAE Contact Details
insert into [AMGR_User_Fields_Tbl](
[Client_Id], [Contact_Number], [Type_Id],
[Code_Id], [Last_Code_Id], [DateCol],
[NumericCol], [AlphaNumericCol],
[Creator_Id], [Create_Date], [mmddDate],
[Modified_By_Id], [Last_Modify_Date]
) select
I.[Client_Id], I.[Contact_Number],1091,
0, 0, null,
null, R.USER_PHONE_2,
I.[Creator_Id], I.[Create_Date], I.[mmddDate],
I.[Modified_By_Id], I.[Last_Modify_Date]
FROM [dbo].[AMGR_User_Field_Defs_Tbl] AS S INNER JOIN [dbo].[ADMN_User_Details] AS R
ON S.DESCRIPTION = R.user_firstname + ' ' + R.user_name
INNER JOIN INSERTED AS I
ON I.CODE_ID = S.CODE_ID
insert into [AMGR_User_Fields_Tbl](
[Client_Id], [Contact_Number], [Type_Id],
[Code_Id], [Last_Code_Id], [DateCol],
[NumericCol], [AlphaNumericCol],
[Creator_Id], [Create_Date], [mmddDate],
[Modified_By_Id], [Last_Modify_Date]
) select
I.[Client_Id], I.[Contact_Number],1094,
0, 0, null,
null, R.USER_EMAIL_ADDRESS,
I.[Creator_Id], I.[Create_Date], I.[mmddDate],
I.[Modified_By_Id], I.[Last_Modify_Date]
FROM [dbo].[AMGR_User_Field_Defs_Tbl] AS S INNER JOIN [dbo].[ADMN_User_Details] AS R
ON S.DESCRIPTION = R.user_firstname + ' ' + R.user_name
INNER JOIN INSERTED AS I
ON I.CODE_ID = S.CODE_ID
insert into [AMGR_User_Fields_Tbl](
[Client_Id], [Contact_Number], [Type_Id],
[Code_Id], [Last_Code_Id], [DateCol],
[NumericCol], [AlphaNumericCol],
[Creator_Id], [Create_Date], [mmddDate],
[Modified_By_Id], [Last_Modify_Date]
) select
I.[Client_Id], I.[Contact_Number],1089,
0, 0, null,
null, R.USER_PHONE_3,
I.[Creator_Id], I.[Create_Date], I.[mmddDate],
I.[Modified_By_Id], I.[Last_Modify_Date]
FROM [dbo].[AMGR_User_Field_Defs_Tbl] AS S INNER JOIN [dbo].[ADMN_User_Details] AS R
ON S.DESCRIPTION = R.user_firstname + ' ' + R.user_name
INNER JOIN INSERTED AS I
ON I.CODE_ID = S.CODE_ID
insert into [AMGR_User_Fields_Tbl](
[Client_Id], [Contact_Number], [Type_Id],
[Code_Id], [Last_Code_Id], [DateCol],
[NumericCol], [AlphaNumericCol],
[Creator_Id], [Create_Date], [mmddDate],
[Modified_By_Id], [Last_Modify_Date]
) select
I.[Client_Id], I.[Contact_Number],1088,
0, 0, null,
null, R.USER_PHONE_1,
I.[Creator_Id], I.[Create_Date], I.[mmddDate],
I.[Modified_By_Id], I.[Last_Modify_Date]
FROM [dbo].[AMGR_User_Field_Defs_Tbl] AS S INNER JOIN [dbo].[ADMN_User_Details] AS R
ON S.DESCRIPTION = R.user_firstname + ' ' + R.user_name
INNER JOIN INSERTED AS I
ON I.CODE_ID = S.CODE_ID
insert into [AMGR_User_Fields_Tbl](
[Client_Id], [Contact_Number], [Type_Id],
[Code_Id], [Last_Code_Id], [DateCol],
[NumericCol], [AlphaNumericCol],
[Creator_Id], [Create_Date], [mmddDate],
[Modified_By_Id], [Last_Modify_Date]
) select
I.[Client_Id], I.[Contact_Number],1090,
0, 0, null,
null, R.user_Address_Line1 +','+ R.user_Address_Line2 +','+ R.User_City+','+ R.user_state_Province +','+ R.User_Zip_Code,
I.[Creator_Id], I.[Create_Date], I.[mmddDate],
I.[Modified_By_Id], I.[Last_Modify_Date]
FROM [dbo].[AMGR_User_Field_Defs_Tbl] AS S INNER JOIN [dbo].[ADMN_User_Details] AS R
ON S.DESCRIPTION = R.user_firstname + ' ' + R.user_name
INNER JOIN INSERTED AS I
ON I.CODE_ID = S.CODE_ID
END
end
end
----******--------------------------------------------
--- UPDATE DETAILS ON CHANGE OF THE ACCOUNT MANAGER
----******--------------------------------------------
IF UPDATE (Code_Id) AND UPDATE (Type_Id)
BEGIN
SELECT @cClientID3 = [Client_Id], @cContactNumber3 = [Contact_Number], @iTCTypeID3 = [Type_Id], @iTCCodeID3 = [Code_Id] from inserted
-- Get codes for Primary key Account Executive names
SELECT @iTCTypeID9 = [Type_Id], @iTCCodeID9 = [Code_Id], @description9 = [Description] from [AMGR_User_Field_Defs_Tbl] where [Type_Id] = '107' AND [CODE_Id] = @iTCCodeID3 and [type_Id]= @iTCTypeID3
SELECT @Work = User_Phone_1, @Cell = User_Phone_2, @Fax = User_Phone_3, @Email = User_Email_Address ,@PhisicalAddres = user_Address_Line1 +','+ user_Address_Line2 +','+ User_City+','+ user_state_Province +','+ User_Zip_Code FROM ADMN_User_Details WHERE [User_FirstName] + ' ' + [User_Name] = @description9
----- Insert Land line
UPDATE U
SET AlphanumericCol = @Email
FROM AMGR_User_Fields_Tbl U INNER JOIN INSERTED I
ON U.Client_id = I.Client_id
WHERE U.TYPE_ID = 1094
UPDATE U
SET AlphanumericCol = @Work
FROM AMGR_User_Fields_Tbl U INNER JOIN INSERTED I
ON U.Client_id = I.Client_id
WHERE U.TYPE_ID = 1088
UPDATE U
SET AlphanumericCol = @Cell
FROM AMGR_User_Fields_Tbl U INNER JOIN INSERTED I
ON U.Client_id = I.Client_id
WHERE U.TYPE_ID = 1091
UPDATE U
SET AlphanumericCol = @Fax
FROM AMGR_User_Fields_Tbl U INNER JOIN INSERTED I
ON U.Client_id = I.Client_id
WHERE U.TYPE_ID = 1089
UPDATE U
SET AlphanumericCol = @PhisicalAddres
FROM AMGR_User_Fields_Tbl U INNER JOIN INSERTED I
ON U.Client_id = I.Client_id
WHERE U.TYPE_ID = 1090
--end
-- End
END
END
GO
February 10, 2012 at 6:39 am
First, the "IF Update" probably isn't doing what you want because an Insert will count every column as being updated. It looks like you're trying to use "IF Update" to differentiate between inserts and updates, and it doesn't work that way.
If that's what you're trying to do, you're better off with two triggers: one for inserts, one for updates. Alternatively, you can compare the Inserted and Deleted tables, and if there's anything in the Deleted table, it's an update, and if not, it's an insert. But if the logic for the two is substantially different, make two different triggers. Common code between them can be put in a stored procedure and the triggers can both call that procedure for whatever they have in common, and do separate work for whatever they do that's different.
Second, this trigger assumes that you will NEVER update or insert more than one row at a time. The trigger should either verify that and raise an error if there's more than one row in the Inserted table, or should be built to deal with multiple rows being updated/inserted at once. By assigning values to variables out of the Inserted table, you are assuming there will only ever be one row in there, and that is likely to cause horrible problems.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2012 at 6:52 am
RE: Common code between them can be put in a stored procedure and the triggers can both call that procedure for whatever they have in common, and do separate work for whatever they do that's different.
Can you please share an example to work on this - may be work on my scenario, i gave it a thought but i just couldn't figure out how i should go about it.
Thanks in advance
February 10, 2012 at 7:24 am
A trigger can call a stored procedure, just use Exec the same way you would in any other place where you call a proc.
If the proc will need access to the Inserted and Deleted objects, you can Select Into from those to a temp table, in the trigger, and the proc will have access to those temp tables. Won't have access to Inserted/Deleted, but the temp tables will be in-scope.
For example:
create proc dbo.MyTriggerProc
as
set nocount on;
insert into dbo.MyLogTable (Column1, Column2)
select C1, C2
from #Inserted;
GO
create trigger MyTrigger on dbo.MyTable
after insert
as
set nocount on;
select *
into #Inserted
from Inserted;
exec dbo.MyTriggerProc;
That example won't do anything useful, of course, but it illustrates the method.
Of course, the proc can have parameters, and you can pass those over from the trigger in the normal fashion. Temp tables with the Inserted/Deleted contents are probably going to be more useful than parameters though.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply