Codeigniter and SQL Server last inserted ID

Hello Community,

I am back again with another question. Please guide me in my new problem. 


Here is my scenario:

I have two tables.

1. role
    - id
    - name
2. role_detail
    - id
    - role_id
    - module_id

These two tables are linked to each other by (role.id) and (role_detail.role_id)

Now, I have a form where I will create a role, and in the detail, I can add what modules, that role can access. (role_detail.module_id)

So here's what I want to do:

After I insert the header (role), I want to get the inserted id. And then I will use that last inserted id in the detail so I can create a reference.

Example:

*role
-------------
id - 1
name - admin


*role_detail
--------------
id - 1
role_id - 1
module_id - 1

id - 2
role_id - 1
module_id - 2

id - 3
role_id - 1
module_id - 2

In the example the last inserted id in the (role) table is 1. and in the (role_detail) table I used that last inserted in the role_id column. Now I can join the two tables.

Would you help me solve this? If I am using MySql there will be no problem.

So my question is, how can I use the last inserted id, and fetch in CodeIgniter if I am using MS SQL Server as database? Hope someone who has experienced the same scenario would help me.

I hope I explained it clearly. Thanks for your suggestions. I appreciate it.

would this not work?

$maxID = $this->db->select("max(id) as maxid")->from("role")->get()->row()->maxid;