On Thu, Sep 9, 2010 at 12:26 PM, David Connors <[email protected]> wrote:
> Oh, my example is Transact-SQL - sorry. NFI if it will work on Jet but give > it a whirl and let me know. > > Tested it. The trigger is on the SQL Server and it does work (as expected) when updates are made via Access. Cheers Dave > -- > David Connors > Software Engineer > Codify Pty Ltd - <http://www.codify.com>www.codify.com > Phone: +61 (7) 32106269 | Facsimilie: +61 (7) 32106269 Mobile: +61 > 417189363 > Address Info: <http://www.codify.com/AboutUs/ContactDetails> > http://www.codify.com/AboutUs/ContactDetails > This message was sent from my phone. Please excuse the brevity. > > On 09/09/2010, at 12:24 PM, David Burstin <[email protected]> wrote: > > 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]> > [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]> >> [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]> >>> [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 >> >> >
