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

General SQL Server Forums

New to SQL Server Programming

after update , insert

Author

Topic

Sally1053
Starting Member

7 Posts

Posted - 2012-02-09 : 07:05:30

hi

please check the code, am not not why its not working

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

SR Masipa

Kristen
Test

22859 Posts

Posted - 2012-02-09 : 07:16:04

This is exactly the same as the post you made three days ago, isn't it?:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=171020

Please don't waste our time starting new threads for the same question.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-09 : 07:26:50

Bad architecture. A TRIGGER should NEVER be this complicated..BESIDES a HOST of very poorly designed code

Maybe a Gila Monster will stop by

MOO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Sally1053
Starting Member

7 Posts

Posted - 2012-02-09 : 07:34:05

Please provide recommendation on how to go about this, I need your help guys.

SR Masipa

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-09 : 07:41:27

Are you designing something new? Or have you inherited a mess and have to figure out why it's not working?

Do you allow only Sproc Access to your Data?

Or is the wild west and everyone does dynamic sql and has dbo rights?

Or worse, sa rights

????

Which on eis it?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Sally1053
Starting Member

7 Posts

Posted - 2012-02-09 : 07:52:45

the design of the database is a mess and have no control over bacause its an external vendor product.

you have only one main table that contain the data and have another table that contain defined attributes.

Looking at my trigger i am appending new records on new insertion of data

and again update such records and one update values

Basically; When you assign a user to a profile, user contact detail mu populate automatically and when you reassign the user to someone else then the contact deails should get updated to match the new user.

SR Masipa

Kristen
Test

22859 Posts

Posted - 2012-02-09 : 12:38:20

quote:
Originally posted by X002548

Bad architecture. A TRIGGER should NEVER be this complicated..BESIDES a HOST of very poorly designed code

I had already said that in the other thread the first time it was posted Brett ... O/P didn't answer the question I asked in that thread but just posted the original question in this new thread instead.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=171020

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources