I have following trigger that creates 4 agents in my agent table. I am using
this to hash my passwords

UPDATE Agent SET passwd = dbo.ud_MakeSHA1(Passwd)


If I place that at the end of my trigger it hashes all passwords in the
agent table I need to only has the new entires as they are inserted. How can
I do that?


CREATE TRIGGER dbo.trig_AgentInsert
ON dbo.Agent
FOR INSERT

AS

SET NOCOUNT ON

-- DROP TABLE #Agent

SELECT *
INTO #Agent
FROM Inserted
WHERE AgencyCode = Name
ORDER BY Name

-- Update DMSUser - force to 'Y' for FL GA IL
UPDATE #Agent
SET DMS_User = 'Y'
WHERE State IN ( 'FL' , 'GA' , 'IL' )

-- Insert for base code
INSERT INTO dbo.Agent
( AgentCodeID
, Passwd
, Name
, AgencyCode
, GroupID
, FullName
, Email
, SubProducerCode
, SmGroup
, State
, OfficeID
, DMS_User
, Library_Info
, Library_desc
, Cabinet
, LastUpdateDateTime )
SELECT AgentCodeID = Src.AgentCodeID
, Passwd = Src.Passwd
, Name = Src.Name
, AgencyCode = Src.AgencyCode
, GroupID = Src.GroupID
, FullName = Src.FullName
, Email = Src.Email
, SubProducerCode = Src.SubProducerCode
, SmGroup = Src.SmGroup
, State = Src.State
, OfficeID = Src.OfficeID
, DMS_User = Src.DMS_User
, Library_Info = Src.Library_Info
, Library_desc = Src.Library_desc
, Cabinet = Src.Cabinet
, LastUpdateDateTime = GETDATE()
FROM #Agent Src
LEFT JOIN dbo.Agent Tgt
ON Src.Name = Tgt.Name
WHERE Tgt.Name IS NULL
ORDER BY Name

UPDATE #Agent
SET Name = RTRIM( AgencyCode ) + 'prin'

-- SELECT Name FROM #Agent

-- Insert for prin
INSERT INTO dbo.Agent
( AgentCodeID
, Passwd
, Name
, AgencyCode
, GroupID
, FullName
, Email
, SubProducerCode
, SmGroup
, State
, OfficeID
, DMS_User
, Library_Info
, Library_desc
, Cabinet
, LastUpdateDateTime )
SELECT AgentCodeID = Src.AgentCodeID
, Passwd = Src.Passwd
, PrinName = RTRIM( Src.AgencyCode ) + 'prin'
, AgencyCode = Src.AgencyCode
, GroupID = Src.GroupID
, FullName = Src.FullName
, Email = Src.Email
, SubProducerCode = Src.SubProducerCode
, PrinSMGroup = 'principal'
, State = Src.State
, OfficeID = Src.OfficeID
, DMS_User = Src.DMS_User
, Library_Info = Src.Library_Info
, Library_desc = Src.Library_desc
, AgencyCabinet = 'Agency'
, LastUpdateDateTime = GETDATE()
FROM #Agent Src
LEFT JOIN dbo.Agent Tgt
ON Src.Name = Tgt.Name
WHERE Tgt.Name IS NULL
ORDER BY Src.Name

UPDATE #Agent
SET Name = RTRIM( AgencyCode ) + 'admn'

-- SELECT Name FROM #Agent

-- Insert for admn
INSERT INTO dbo.Agent
( AgentCodeID
, Passwd
, Name
, AgencyCode
, GroupID
, FullName
, Email
, SubProducerCode
, SmGroup
, State
, OfficeID
, DMS_User
, Library_Info
, Library_desc
, Cabinet
, LastUpdateDateTime )
SELECT AgentCodeID = Src.AgentCodeID
, Passwd = Src.Passwd
, AdmnName = RTRIM( Src.AgencyCode ) + 'admn'
, AgencyCode = Src.AgencyCode
, GroupID = Src.GroupID
, FullName = Src.FullName
, Email = Src.Email
, SubProducerCode = Src.SubProducerCode
, AdmnSMGroup = 'admin'
, State = Src.State
, OfficeID = Src.OfficeID
, DMS_User = Src.DMS_User
, Library_Info = Src.Library_Info
, Library_desc = Src.Library_desc
, AgencyCabinet = 'Agent'
, LastUpdateDateTime = GETDATE()
FROM #Agent Src
LEFT JOIN dbo.Agent Tgt
ON Src.Name = Tgt.Name
WHERE Tgt.Name IS NULL
ORDER BY Src.Name

UPDATE #Agent
SET Name = RTRIM( AgencyCode ) + 'prod'

-- SELECT Name FROM #Agent

-- Insert for prod
INSERT INTO dbo.Agent
( AgentCodeID
, Passwd
, Name
, AgencyCode
, GroupID
, FullName
, Email
, SubProducerCode
, SmGroup
, State
, OfficeID
, DMS_User
, Library_Info
, Library_desc
, Cabinet
, LastUpdateDateTime )
SELECT AgentCodeID = Src.AgentCodeID
, Passwd = Src.Passwd
, ProdName = RTRIM( Src.AgencyCode ) + 'prod'
, AgencyCode = Src.AgencyCode
, GroupID = Src.GroupID
, FullName = Src.FullName
, Email = Src.Email
, SubProducerCode = Src.SubProducerCode
, ProdSMGroup = 'agent'
, State = Src.State
, OfficeID = Src.OfficeID
, DMS_User = Src.DMS_User
, Library_Info = Src.Library_Info
, Library_desc = Src.Library_desc
, AgentCabinet = 'Agent'
, LastUpdateDateTime = GETDATE()
FROM #Agent Src
LEFT JOIN dbo.Agent Tgt
ON Src.Name = Tgt.Name
WHERE Tgt.Name IS NULL
ORDER BY Src.Name

DROP TABLE #Agent

SET NOCOUNT OFF

/*
SELECT * FROM Sitemndr.dbo.Agent WHERE AgentCodeID = 9393

INSERT INTO Sitemndr.dbo.Agent
( AgentCodeID
, Passwd
, Name
, AgencyCode
, GroupID
, FullName
, Email
, SubProducerCode
, SmGroup
, State
, OfficeID
, DMS_User
, Library_Info
, Library_desc
, Cabinet
, LastUpdateDateTime )
VALUES ( 9393
, 'Test'
, 'Test'
, 'Test'
, 9393
, 'Test'
, 'Test'
, 'Test'
, 'Test'
, 'CA'
, '9393'
, 'Y'
, 'Info'
, 'Desc'
, 'Cabinet'
, GETDATE() )

DELETE FROM Sitemndr.dbo.Agent WHERE AgentCodeID = 9393
*/

Lontae Jones wrote:

Join the Agent table with the virtual inserted table so only the rows
inserted are affected.

--
David Gugick
Imceda Software
www.imceda.com

Can you give me an example?

"David Gugick" wrote:

Lontae Jones wrote:

Try something like the following. You need to join the tables together
on the PK. inserted is a virtual table accessible from updated and
inserted triggers and contains the new or updated rows. Another virtual
table "deleted" is available from delete and update triggers and
contains the deleted rows. Columns match the changed table.

Update Agent
Set password = dbo.ud_MakeSHA1(b.Password)
From Agent inner join inserted b
On Agent.PK = b.PK

--
David Gugick
Imceda Software
www.imceda.com

Hi

You should start by looking at the "Create Trigger" topic in Books online
(which should be on your start menu or downloadable from
http://www.microsoft.com/downloads/d...DisplayLang=en)

This gives an example of auditing (which seems to be what you are looking
for). You will have to create a trigger for each table you wish to audit.

John

"vichet" <vichetchea (AT) everyday (DOT) com.kh> wrote