AFTER UPDATE, INSERT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

AFTER UPDATE, INSERT

Author

Topic

Sally1053
Starting Member

7 Posts

Posted - 2012-02-06 : 02:38:00

Hi

Please urgently review the script below for me, It does insert records but doesn't not update information.

It only update if i comment out the insert statement.

---
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
----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 KAE 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

SR Masipa

Kristen
Test

22859 Posts

Posted - 2012-02-06 : 03:27:04

NOTE: Your trigger only works with one row. Triggers need to be written to handle multiple rows - the INSERTED & DELETED pseudo-tables will contain ALL the rows in the Insert/Update.

IF UPDATE (Code_Id) AND UPDATE (Type_Id)

does not tell you that the column has changed, ONLY that the column was part of the UPDATE statement (so it may be likely that it has changed, but it is not certain).

I can't see why it would work for UPDATE only if you comment out the "INSERT"

Sally1053
Starting Member

7 Posts

Posted - 2012-02-06 : 03:32:30

They all run if I comment one or the other. Its only when consolidated or combined that the first code is the one that triggers and the bottom does not.

SR Masipa

Sally1053
Starting Member

7 Posts

Posted - 2012-02-06 : 03:34:39

IF UPDATE (Code_Id) AND UPDATE (Type_Id)

does not tell you that the column has changed, ONLY that the column was part of the UPDATE statement (so it may be likely that it has changed, but it is not certain).

hI Kriten,

R u suggestng that i should use Deleted table and not inserted

SR Masipa

Kristen
Test

22859 Posts

Posted - 2012-02-06 : 03:42:41

"They all run if I comment one or the other. Its only when consolidated or combined that the first code is the one that triggers and the bottom does not."

Just to be sure I have not misunderstood you are ONLY talking about changing this line:

AFTER INSERT,UPDATE

to either

AFTER INSERT
or
AFTER UPDATE

?? (Rather than commenting out some other code). If so I can't see the cause, sorry, hopefully someone else will be able to.

"R u suggestng that i should use Deleted table and not inserted"

If you want to know FOR SURE that a column has changed you will have to compare the column between DELETED and INSERTED. However, that may not be important to you UPDATE() tells you the column was updated, it just might have been updated with the same data as before.

Don't overlook the fact that your trigger does not handle multiple rows, that is critical in the construction of a trigger

Sorry, but the whole structure of the trigger is a bit of a dog's breakfast. TRIGGERs need to be very efficient because they are called as part of core inserts / update to the table, and this "get an id, lookup a row, insert three rows separately, optionally update three rows separately" is not very efficient and could probably be replaced with one insert and one update (which could also process as many rows as were being inserted or updated in the batch)

Sally1053
Starting Member

7 Posts

Posted - 2012-02-10 : 00:38:52

TRIGGERs need to be very efficient because they are called as part of core inserts / update to the table, and this "get an id, lookup a row, insert three rows separately, optionally update three rows separately" is not very efficient and could probably be replaced with one insert and one update (which could also process as many rows as were being inserted or updated in the batch)

------>>>>> May you please provide me with the code to use in my scenario, am not quite following what you want me to do here since am inserting rows separetely already.

Just to be sure I have not misunderstood you are ONLY talking about changing this line:

AFTER INSERT,UPDATE

to either

AFTER INSERT
or
AFTER UPDATE

----->>>> i am not changing the line, the line stays the same as AFTER INSERT,UPDATE when running the individual codes. i am only commenting out some of the code and it run well , however when both are included one does not run. normally the code in the bottom.

Please help me in finding a way to get this right and also considering performance efficiency,

Thank you in advance

SR Masipa

Kristen
Test

22859 Posts

Posted - 2012-02-10 : 03:42:57

The logic for your code is basically:


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
			... Some INSERT statements ...
		END
	END
END

IF UPDATE (Code_Id) AND UPDATE (Type_Id)
BEGIN
	... Some UPDATE statements ...
END

if the INSERT or UPDATE sections are not running it is because the IF condition is false.

You can put some PRINT statements in your code to examine what is going on, and then make some test Updates/Inserts to see what values you get, e.g.


PRINT '@iTCCodeID9=' + CONVERT(varchar(20), @iTCCodeID9) + ', @iTCCodeID3=' + CONVERT(varchar(20), @iTCCodeID3)
IF  @iTCCodeID9 = @iTCCodeID3
BEGIN
PRINT '@cContactNumber3 =' + CONVERT(varchar(20), @cContactNumber3 )
	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
PRINT 'EXISTS = true, INSERT is running'
			... Some INSERT statements ...
		END
	END
END

PRINT 'UPDATE(Code_Id)=' + CONVERT(varchar(20), UPDATE(Code_Id)) + ', UPDATE(Type_Id)=' + CONVERT(varchar(20), UPDATE(Type_Id))
IF UPDATE (Code_Id) AND UPDATE (Type_Id)
BEGIN
PRINT 'UPDATE is running'
	... Some UPDATE statements ...
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources