January 29, 2010 at 8:40 am
Hello - I need some help with triggers. I have a table for inventory that includes the employee name and employee number. My objective is that when I enter the TM_Number to the table, I need a trigger to pull their name out of Active Directory and update the table. I have included the Active Directory query that will pull the required data I need, and a script for the table with some data.
Originally, this was a simple Access database that has grown. I have tried to use Access but can not access the Active Directory at all. The TM_Number is a new column and the Workstation_User name has been entered first then last, or last then first, or not at all. There is no correlation between this database and Active Directory.
I have the Active Directory linked to the database but when I try to create the trigger the query errors out. Any help would be appreciated.
--Create the Table and populate
create table WorkstationX(
Workstation_PK int,
name varchar (50),
Service_Tagvarchar (50),
Workstation_User varchar (50),
TM_Numbervarchar (50))
insert into WorkstationX (Workstation_PK, name, Service_Tag, Workstation_User, TM_Number)
values (1, 'HMM-PC-001', 'XW40420', '', '')
GO
insert into WorkstationX (Workstation_PK, name, Service_Tag, Workstation_User, TM_Number)
values (2, 'HMM-PC-021', 'XW14420', '', '')
GO
insert into WorkstationX (Workstation_PK, name, Service_Tag, Workstation_User, TM_Number)
values (3, 'HMM-PC-031', 'XW24020', '', '')
GO
insert into WorkstationX (Workstation_PK, name, Service_Tag, Workstation_User, TM_Number)
values (4, 'HMM-PC-041', 'XW80026', '', '')
GO
insert into WorkstationX (Workstation_PK, name, Service_Tag, Workstation_User, TM_Number)
values (5, 'HMM-PC-051', 'XW70060', '', '')
GO
insert into WorkstationX (Workstation_PK, name, Service_Tag, Workstation_User, TM_Number)
values (6, 'HMM-PC-061', 'XW90620', '', '')
GO
select * from WorkstationX
--Active Directory Query
SELECT samAccountName As TM_Number
,displayName AS Workstation_User
FROM OPENQUERY(ADSI, '
SELECT samAccountName, displayName
FROM ''LDAP://DC=ABCD,DC=WXYZ,DC=net''
WHERE objectClass=''user'' AND objectClass<>''computer''
') AS tblADSI
--UserList data
TM_Number Workstation_User
HMM103310 Smith, Luke C HMMA/General Purchasing
HMM100517 Jones, Phillip M HMMA/Plant Engineering
HMM101223 Johnson, Jeremy HMMA/Network Admin
HMM100275 Jackson, Alfred HMMA/Plant Engineering
HMM102854 Kilroy, Jimmy S HMMA/Paint
HMM103518 Kiln, ShaNada D HMMA/General Purchasing
January 29, 2010 at 8:44 am
Instead of trying to store the AD data in the table, why not just query that when you need it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2010 at 9:08 am
Management wants the inventory database complete. They are leasing computers and have a need to know who has what computer and their location. We are leasing around 2500 here and another 1000 at a different location.
January 29, 2010 at 9:14 am
I don't think a trigger is the place to do this. Instead, I'd build a job that can read AD, and update changed rows once a day. Knowing who owns the computer might be good, but it's not necessarily a real-time need. And it's not something that should prevent someone from entering data.
Use a process (or even a reminder) to update this periodically
January 29, 2010 at 9:58 am
Thanks, Steve. I was thinking along the same lines. I'll see if management will go along. If not, I may still need some help.
January 29, 2010 at 12:07 pm
For what it's worth, I agree with Steve. Even if you query AD multiple times throughout the day using this process to update the table, it would be better than using a trigger in this scenario.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy