Agreed that stored procedures would be a better solution. Unfortunately this is an Access application where users can directly access the tables, so I need the field updated whether the insert is done via stored procedure or directly. This is definitely a case where the limitations of using Access to a SQL back end are apparent.
Cheers Dave On Thu, Sep 9, 2010 at 12:20 PM, David Connors <[email protected]> wrote: > CREATE TRIGGER [dbo].[SomeTrigger] > ON [dbo].[Table_1] > INSTEAD OF INSERT > AS > BEGIN > -- SET NOCOUNT ON added to prevent extra result sets from > -- interfering with SELECT statements. > SET NOCOUNT ON; > > -- Insert statements for trigger here > > INSERT INTO Table_1 > SELECT * from inserted <-- Mung your data or pull it from another source > here. > > END > > David. > > PS: Triggers are evil and you're going straight to hell for using one, > shortly followed by me for providing this solution. > > PPS: Used a storage procedure for data access and put whatever logic is > required there wrapped in a transaction etc. > > On 9 September 2010 12:10, David Burstin <[email protected]> wrote: > >> Sorry, that should say I tried an Insert trigger... >> >> >> On Thu, Sep 9, 2010 at 12:10 PM, David Burstin >> <[email protected]>wrote: >> >>> Say I have a table A which has 2 relevant columns (others columns include >>> key and other data): >>> >>> SerialNumber, TimesOnTableB >>> >>> Table B has a SerialNumber column. >>> >>> A.TimesOnTableB = (SELECT COUNT(*) FROM TableB WHERE B.SerialNumber = >>> A.SerialNumber) >>> >>> How can I update A.TimesOnTableB every time a new record is inserted to >>> TableA (NOT TableB)? >>> >>> I tried an update trigger on TableA, but an update trigger can't modify >>> the data that is being updated (or am I wrong about this). >>> >>> Any suggestions welcome. >>> >>> Cheers >>> Dave >>> >> >> > > > -- > *David Connors* | [email protected] | www.codify.com > Software Engineer > Codify Pty Ltd > Phone: +61 (7) 3210 6268 | Facsimile: +61 (7) 3210 6269 | Mobile: +61 417 > 189 363 > V-Card: https://www.codify.com/cards/davidconnors > Address Info: https://www.codify.com/contact > >
