Will a trigger work

Question

Will a trigger work

Charlie-120533

SSCrazy

Points: 2187
More actions
January 29, 2010 at 8:40 am

#137792

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 2

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.

Steve Jones - SSC Editor SSC Guru Points: 734649 More actions · Answer 3

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

Charlie-120533 SSCrazy Points: 2187 More actions · Answer 4

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

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