triggers for generating userID based on user's information.

Question

I have complaint table
1. tblProfile with columns userId | name | age | address | mobileno |
2. tblUserId with columns userId | role | status

now when user fills the form I want to insert one row in tblProfile, before inserting a new row I want to create userId by combining starting letters of name and mobile no and then insert into tblprofile with userId after this I want to insert that UserId into tblUserId table.
for this I have to use two triggers one is before insert trigger and another is after insert trigger.but I dont know how to capture user information to create userId and how to pass that Id to second trigger.

Answer 1

Since you're acting upon INSERTs, try #inserted or inserted as the container of your inserting values.

CREATE TRIGGER...
    insert into tblProfile (userId, name, age, address, mobileno) (
        select N'do-your-concat-here...', name, age, address, mobileno
            from inserted
    )

I have not worked with triggers for a long time now, but this should help you get what you need.

Please have an eye out this link: Using the inserted and deleted Tables.

However, you already have all the tools on the application-side as you have the information and the capability to work with the in-memory information data.

EDIT #1

how to pass this Id to second trigger?

In this particular situation, I see more suitable to process with a stored procedure than two independant triggers. We have interdependent information data here (userId). I do think the simplest way would be a stored procedure. It is adviseable to wrap these operation within a transaction scope, as if either insert fails, both won't be applied, assuring data integrity.

CREATE PROCEDURE prcInsProfileUserId
    -- Assuming data types. Replace with proper data types as needed.
    @name nvarchar(50) NOT NULL 
    , @age int NOT NULL
    , @address nvarchar(150) NOT NULL
    , @mobileno bigint NOT NULL
    , @role nvarchar(10) NOT NULL
    , @status int NOT NULL
AS BEGIN TRANSACTION
    DECLARE @UserId nvarchar(10)
    SET @UserId = N'do-your-concat-here...';

    -- We then have the userId value, so we may insert into both tables accordingly.
    insert into tblProfile (userId, name, age, mobileno) 
        values (@userId, @name, @age, @address, @mobileno)

    insert into tblUserId (userId, role, status)
        values (@userId, @role, @status)

    COMMIT
END

However, if you do prefer to go with triggers, then the alternative would be to insert the concatenated value for userId into a temporary table. You then should have a DDL looking as follows:

DECLARE @UserIdTempTable TABLE (
    userId nvarchar(10) NOT NULL
)

Then, within the first trigger, you would have to set the value of a @userId variable to contain you concatenated userId, then use it to insert into tblProfile, then perform a second insert into @UserIdTempTable.

CREATE TRIGGER...
    DECLARE @userId nvarchar(10)
    SET @userId = N'do-your-concat-here...'

    insert into tblProfile (userId, name, age, address, mobileno) (
        select @userId, name, age, address, mobileno
            from inserted
    )

    IF @@ROWCOUNT > 0
    BEGIN
        delete from @UserIdTempTable -- assuring there is no mistake possible while populating and retrieving the userId
        insert into @UserIdTempTable (userId)
            values (@userId)
    END
END

Then, you may select it from your second trigger.

CREATE TRIGGER second...
    insert into tblUserId (userId, role, status) (
        select tmp.userId, i.role i.status
            from @UserIdTempTable tmp
                , inserted i
    )

Be careful here though, because no data integrity is absolutely preserved as the first insert may have been processed successfully, but the second not. To preserve data integrity, you would have to verify whether @@ROWCOUNT is greater than 0, unless you would delete the record with this actual userId from tblProfile.

This is abosolute hard hand-work. Processing through triggers is not adviseable here, because within the insertion of tblProfile, you do not have information data required by tblUserId, so you have to have two DbCommand and launch two ExecuteNonQuery() in a row. That is a lot of overhead for such a tiny task. Then, it would be more aviseable to process with the stored procedure as suggested, plus it assures data integrity by the DBMS itself, instead of simulating it through a @@ROWCOUNT verification.

Disclaimer: This code is provided as-is and is not guaranteed to compile without you to adapt it to fit your situation. I wrote it off the top of my head with no verification.

I do hope this helps! =)