August 25, 2011 at 8:25 am
Hi,
I need via a trigger (after insert), insert a record into another table .. until then it would be simple, but I need to insert to a table is a given schema. For this I get the name of the user who originated the insert to the table, which in my model I'm using the same schema name. By this I identify which user performed the insert, I do the insert in the second table for the User schema. I needed something like this:
1) receives the name of the User/schema in the Trigger.
2) insert into into @schema.Tabela2 (fields) values (values)
Any idea to do this, or other better solution?
Att
Cristiano
August 25, 2011 at 8:28 am
Original_Login() will get you the login name for the current connection.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2011 at 8:37 am
Hi,
Tks for your attention!
One question.. How i get the Schema current of the user? And how should I use a variable that takes the schema of the User to use the insert?
Something like: insert into @schema.table (field1, field2) values ??(1, 1)..
Sorry to simple questions, but I'm starting in sql server..
Tks again!
August 25, 2011 at 8:45 am
Now that's a lot, lot more difficult.
Why? What's the reasoning here for why a single trigger should behave differently depending on who did the insert?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2011 at 8:56 am
You can use CURRENT_USER to get the name of the user doing the insert, and then you can look up that user in sys.database_principals to find the default schema for the user. Beware, though - if the user gets access by its login beiong a member of a Windows group, then you'll only find the group name in sys.database_principals, not the individual members.
John
August 25, 2011 at 9:05 am
I will answer:
I have a structure of the database as follows:
Database: MyDatabase
schema "Common"
table: users
schema "CompanyX"
table: activities
schema "CompanyY"
table: activities
Example: User's schema CompanyX logs in and includes a new User in the Users table of schema "Common". In the Users table, must have a trigger to automatically include a default activity for the user.What I need is that this trigger do the insert for the table activities of the schema "CompanyX" . How to identify in the trigger of the Users table, so that the insert is done in the correct table?
Tks!
August 25, 2011 at 9:14 am
I would strongly recommend that not be done in a trigger. Rather the procedure that creates the user should insert the activity as well.
Still, you're either going to have to do IF statements to check schema and the inserts or you're going to have to mess with dynamic SQL (and the risks of SQL injection, broken ownership chains and all that mess)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2011 at 11:46 am
Hi,
The Users table uses a "identy" for primary key.
The moment to save the User I do not have the key code to perform the insert in another table, so I had the idea of ??holding the trigger.
Any idea how to not using trigger?
Tks.
August 25, 2011 at 12:21 pm
cristiano.testai (8/25/2011)
The Users table uses a "identy" for primary key.
Ok, doesn't change anything
The moment to save the User I do not have the key code to perform the insert in another table, so I had the idea of ??holding the trigger.
Any idea how to not using trigger?
Insert into Users ....
SELECT @user-id = Scope_Identity()
Insert into SomeSchema.Activities ...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2011 at 12:59 pm
Hi, ok!
Just a consideration. If there are two active connections at the same time in the database and two users concurrently perform an insert in the users table, each session will have your ID correctly or there is a possibility after the Select SCOPE_IDENTITY return the same value?
I will be doing the Insert and then Select in the "transaction" of the program (Delphi), and not directly by procedure in SQL Server.
How do I perform a select to get only one record with the value returned? I did the sintax:
Select SCOPE_IDENTITY() from Users and he returned the number of records that the user has in the table and not just 1 (one) record with the value.
Tks for attention.. I'm begining in SqlServer..
August 25, 2011 at 1:06 pm
cristiano.testai (8/25/2011)
Just a consideration. If there are two active connections at the same time in the database and two users concurrently perform an insert in the users table, each session will have your ID correctly or there is a possibility after the Select SCOPE_IDENTITY return the same value?
May I suggest you do some reading on Identity and the functions that affect it? There's a lot in Books Online. I could repeat it all here, but it wouldn't be as good as going to the source.
Books Online = the SQL help files
How do I perform a select to get only one record with the value returned? I did the sintax:
Select SCOPE_IDENTITY() from Users and he returned the number of records that the user has in the table and not just 1 (one) record with the value.
That part of the code I wrote for you was actually complete.
SELECT @user-id = Scope_Identity()
Or just
SELECT Scope_Identity()
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2011 at 1:21 pm
Very Thanks Gail Shaw!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply