I am new in SQL, we have vairous user defined fields, so when the user insert new value to this field , i want new records (other user field to get populated) be inserted relevant to the inserted value FROM another table (lookup)

This field is a drop down list , after insert committed , i want other user defined field to populate/

Scenario. Drop down is a list of users, after selecting and updating then i want contact details of the above selected user be pupolated.

CREATE TRIGGER User_Defined_field_KAE
ON [dbo].[AMGR_User_Fields_Tbl]
AFTER
INSERT

AS

INSERT INTO [dbo].[AMGR_User_Fields_Tbl]
(Client_Id,
Type_Id,
name
SELECT
Client_Id,
'1089',
A.USER_PHONE_1
FROM inserted , [dbo].[ADMN_User_Details] as A
WHERE name = a.user_id;

There is no need to copy all those USER attributes to the PHONE table. In a relation database you must only enter those data once, in a User table record. Later, by JOINing the USER and PHONE tables together, you can show the name of the user next to his Phone numbers.

Your problem with that trigger vanishes as soon as you live by the rules of a relational database.

Don't know what others think about triggers, but I think of them as the GOTO statements of SQL. In my mind, they are there for the rare, rare occurance when you need to do something that just can't be done any other way.

Too often, when I am working with a database with triggers, I UPDATE a record, or INSERT a record, or do something . . . and I go to check the record that I expect to see changed/added, and nothing had happened. Or some other strange behavior occurs and none of my code could have made it happen. After doubting myself and reviewing my code 100 times, I finally remember, oh! a trigger, and sure-enough, that trigger is causing the unexpected behavior.

I don't like triggers.

There are many ways to accomplish what you are trying to accomplish, without triggers.

As a beginner (as am I), I suggest that you not get into the habit of relying upon them.

Just my two cents.

Please reviw what is wrong or best possible solution to my update statement. Only the last update execute , it does not matter the order of the update statement the last one is the one that does the work and the first ones do not update as expected

My deadline is approaching , please urently help

ALTER TRIGGER User_Defined_field_KAE
ON [dbo].[AMGR_User_Fields_Tbl]
AFTER
INSERT

AS

DECLARE @CLIENTID Varchar(24);
set @CLIENTID = (select Client_Id from inserted);

INSERT INTO [dbo].[AMGR_User_Fields_Tbl]
(Client_Id,
Contact_number,
Type_Id ,
code_id,
NumericCol,
creator_id,
Create_date,
Modified_By_id,
Last_Modify_date)
SELECT
@CLIENTID,
0,
1088,
0,
0,
system_user,
getdate(),
system_user,
getdate()
FROM inserted I,[dbo].[AMGR_User_Field_Defs_Tbl] AS A
WHERE I.Type_Id = a.Type_Id
AND A.code_id = I.code_id;



INSERT INTO [dbo].[AMGR_User_Fields_Tbl]
(Client_Id,
Contact_number,
Type_Id ,
code_id,
NumericCol,
creator_id,
Create_date,
Modified_By_id,
Last_Modify_date)
SELECT
I.Client_Id,
0,
1091,
0,
0,
system_user,
getdate(),
system_user,
getdate()
FROM inserted I, [dbo].[AMGR_User_Field_Defs_Tbl] AS A
WHERE I.Type_Id = a.Type_Id
AND A.code_id = I.code_id;


UPDATE [dbo].[AMGR_User_Fields_Tbl]
SET AlphanumericCol = (select USER_PHONE_2
FROM [dbo].[ADMN_User_Details] AS R inner join [dbo].[AMGR_User_Field_Defs_Tbl] AS S
ON S.Description = r.user_firstname + ' ' + r.user_name
WHERE S.Type_Id = 107 AND
S.code_id = 58
and [dbo].[AMGR_User_Fields_Tbl].client_id = @CLIENTID
and [dbo].[AMGR_User_Fields_Tbl].type_id = 1091);

UPDATE [dbo].[AMGR_User_Fields_Tbl]
SET AlphanumericCol = (select USER_PHONE_3
FROM [dbo].[ADMN_User_Details] AS R inner join [dbo].[AMGR_User_Field_Defs_Tbl] AS S
ON S.Description = r.user_firstname + ' ' + r.user_name
WHERE S.Type_Id = 107 AND
S.code_id = 58
and [dbo].[AMGR_User_Fields_Tbl].type_id = 1088
and [dbo].[AMGR_User_Fields_Tbl].client_id = @CLIENTID);

Thanks in advance.

Unfortunately the design to some point does not allow me to do like you saying.there no link between the user_details table and User_Fields

This whole problem makes very little sense to me.

When I look at the code,
- after each insert of one record in the AMGR_User_Fields_Tbl,
- insert two other records in the very same AMGR_User_Fields_Tbl table, one with type_id = 1091 the other with type_id = 1088

Do you really want to end up with 3 new records in the AMGR_User_Fields_Tbl table after each new insert? What's the point of it? Is your system still in design phase?

Can you replace the ????? in this code so it is possible to make a junction between the AMGR_User_Fields_Tbl and ADMN_User_Details tables?

If you're serious that there is no link between the user_details table and User_Fields, I see no way you can make this work.
Inform your boss you need a good database designer.

---------------------------------------------------------------------------------------------

This application is off the shelf CRM, we cant change the design.The application comes with customisation for users to create their own user fields, so in this instance 1088 is Tel.Number and 1099 is Cell number. So table User_fields contains the data for all the user fields created.

The user_detail table is a table where you create the users(account Managers) to use the application and is not linked to any table. Except you can trace the user changes by Loginname on the application.

We have client table where that contain a list of company(customers) names and an account manager can manage one or more portfolio's (customers). There is a user field created called Account manager, which is a tabular list with names from the user (account manaer) table. So when you select the name here , i want to automatically populate the account manager details and link them to the customer portfolio. Hence why am inserting those attributes.

The problem comes when i have to update the alphanumeric fields because there is no way link btn the user-detail table n the user_fields or user_fields-defs.

Hence why after inserting am trying to go back and now update those alphanuricCol accordingly.

Now i have notice that having two updates statement the last one overwrite the first one, so i want some kind of loop function to mae this update using on update statemeNT.

We can only link

[AMGR_User_Fields_Tbl] with [AMGR_User_Field_Defs_Tbl] on typeID

and

[AMGR_User_Field_Defs_Tbl] with [ADMN_User_Details] onS.Description = r.user_firstname + ' ' + r.user_name


AMGR_User_Fields_Tbl] and [ADMN_User_Details] cannot be linked at all.


Something else that i have just picked is that code_id may change so i need it to change where possible based on the TypeId in[AMGR_User_Field_Defs_Tbl] table. We have a recordId field in this table that uniquely identify each record but it is not linked to any table.

Just to elaborate more how the type_id and code_id work. When the user create a user field, the data type could be Varchar, date or table etc.

In case of data type table, users have different options to choose from. The type_Id will be same for every option but the codeId will be different. So this is the case with the account managers in the [AMGR_User_Field_Defs_Tbl] tables. Type_id = 107 and code_id is deffirent per the account manager name.




Thanks in advance.

I feel your pain. One of our systems here is a MS Dynamics system.Are you really sure about that? And I mean REALLY sure? What CRM are you using? In MS Dynamics, all tables are linked by GUIDs.
Linking two tables by using the full name of people is soooo bad. I can hardly believe a CRM system would not provide proper FK's. But a CRM will most likely use obfuscating names for those columns. "Obfuscating" for us, not necessarily obfuscating for the developers of the CRM system.

Put on your Sherlock Holmes hat and start digging in your tables. Take food and water for three days and only come back when you have found the PK's and FK's of the tables you mentioned so far.
- When you do a SELECT TOP 100 * FROM AMGR_User_Fields_Tbl (or another table name), do you see GUIDs (like 'F09C92A3-3319-4207-A113-49F759AC99FB')?. Or other possible ID's, like BIGINTs?
- JOIN two tables together by whatever means you have, like onS.Description = r.user_firstname + ' ' + r.user_name or something else. Try to figure out what other columns (preferably GUID, BIGINT or INT columns) also contain the same values (if not in all cases, in many cases). Try to use those columns to JOIN the tables together. Does the result makes sense?

In CRM systems, the UI is also provided by the CRM system. Normally the CRM system should take care of storing and retrieving extra user fields to and from the database. I really wonder why you would have to do that yourself.

I would first try to use a stored procedure instead of a trigger.

Your trigger fires when the system INSERTS a new record into AMGR_User_Fields_Tbl, but your trigger will INSERT two new records in the very same AMGR_User_Fields_Tbl table, firing your trigger twice. And those two fired triggers will INSERT another 4 new records in the same table, firing your trigger 4 times over, .... and so on and on.
Read about Recursive Triggers

Hi,

Unfortunately i have checked million times and i cant link the user detail table on any fields except account name or to any other field. The CRM in use is Maximizer by Camsoft.

At the same time we are searching for the best CRM because there are still other things we still question on our CRM.

I will play around with the code and will let u know if i come up with anything and will take it from there.

Do you mind to provide me with your private email address so that if i have question based on your experince with Ms Dynamic funstionality or around sql code i can just pop directly. Am still new with SQL

Thanks for you effort and really appreciated.

Hi there

I need an automated process where i can reset the field value to 1 on a table at the begining of each year (first day of the new year).How would you go around this? Triggers or stored procedure? please help with the code as well for example.

Can you have more than 1 After insert,update triggers on one table and how does it affect the performance and everything around the table.

Thanks in advance.

Send me a PM.

I would suggest to keep posting on this forum. There are a lot of talented people here who will gladly try to help.

You should start a new tread for this question.