insert trigger - sql2005

[onressy]

Hi i'm creating my first 'after update' trigger. SQl server has two dbs that the trigger will be used against. The purpose of the trigger is when a table (10 columns) in db1 has an update the updated values are also updated a table in db2. I've been looking thru msdn articles and other sources, and could not find anything to access what has transpired within a table. I hoping that i would not have to pass values from an application level update statement to the trigger, but rather use some sort of transaction log to in the trigger, so when table on db1 get and update, table in db2 udtaes the specified columns with the same data.

Thanks for your insight!

 

[bborissov]

In the trigget itself use INSERTED table. That table keeps the new values of the fields. So if you have trigger AFTER UPDATE for Table1 in DB1 and you want to update the same table in DB2 with new values, put this in the trigger (but don't forget to make a good backup first)

...
UPDATE db2..Table1 SET Field1 = Ins.Field1,
                       Field2 = Ins.Field2,
                       Field2 = Ins.Field1
FROM db2..Table1 Table1
INNER JOIN INSERTED Ins ON Table1.PK = In.PK

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[onressy]

Thanks for the pointers bborissov. If you wouldn't mind can you look at what if created, can't seem to get it working:

Alter TRIGGER [trgInsert1]

ON [db1].[dbo].[Member]
/* only 1 record ever gets updated at a time*/

FOR UPDATE AS
/* below i'm trying to get the value column of MemberID for the record just updated */

SELECT IDENT_CURRENT('[db1].[dbo].[MemberID]') AS [IDEN]
GO


FOR AFTER UPDATE AS 

/*updating db2 table with db1 table info */

UPDATE [db2].[dbo].[country] SET Country = Ins.Country
FROM [db1].[dbo].[Member]
INNER JOIN INSERTED Ins ON [Member].[Country] = Ins.[Country]
Where [db2].[dbo].[country].[appID] = [IDEN]
GO

 

[onressy]

Ok i have changed the trigger after woking thru the method and applying bborissov's suggestions: this is the error i'm getting, any suggestions:
Msg 1084, Level 15, State 1, Procedure trgInsertCountry1, Line 7
'AFTER' is an invalid event type.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'INSERTED'.

/////
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


Alter TRIGGER [trgInsertCountry1]
ON [db1].[dbo].[Member]


FOR AFTER UPDATE
AS


SELECT IDENT_CURRENT('[db1].[dbo].[MemberID]') AS [IDEN]
GO

UPDATE [db2].[dbo].[country] SET Country = Ins.Country
FROM [db1].[dbo].[Member]
INNER JOIN INSERTED Ins ON [db1].[dbo].[Member].[MemberID] = (SELECT TOP 1 [db2].[dbo].[appID] FROM [db2].[dbo].[CompanyMemberID]
WHERE [db2].[dbo].[CompanyMemberID].[MdaID] = [IDEN])


GO

 

[bborissov]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


Alter TRIGGER [trgInsertCountry1]
ON [db1].[dbo].[Member]
AFTER UPDATE -- Not FOR AFTER, just AFTER
AS
....

Also I am not sure what you want to achiev here. Why you join MEMBER with INSERTED? What you want to have in Country table?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[onressy]

Thanks again bborissov, i have the join because (and maybe there is a better way) Member table like so in db1:
:Member:
MemberID
Country
Gender
Specialty

db2 has table:
:CompanyIDs:
appID - auto increment
MemberID - from db1
otherID

:country:
appID
Country

So db1 and db2 do not have the same structure. The MemberID in db1 is associated to an appID in db2 CompanyIDs(appId is used in all db2 tables to associate the two dbs info.)

Does what i said make sense? Thanks

 

[onressy]

Bump