[dba-SQLServer] Trigger syntax

Hi Francisco

Oh, I did refresh "everything" multiple times, even disconnecting and relaunching SSMS. A bit strange as the created trigger was clearly listed in the object browser.

Thanks for the tip on Redgate.

/gustav


>>> fhtapia at gmail.com 14-06-12 16:38 >>>
I've seen this issue in Management Studio before, you can fix most of these
issues by right clicking on the object explorer on the grouping that you
are working in and refresh, ( thanks microsoft! )

-Francisco
http://bit.ly/sqlthis   | Tsql and More...
Let Apple know you want turn by turn on your iPhone 4!
https://plus.google.com/112803888444646122406/posts/HmQej9pHZQM

<http://db.tt/JeXURAx>




On Tue, Jun 12, 2012 at 1:39 AM, Gustav Brock <gustav at cactus.dk> wrote:

> Hi Jim
>
> Yes, nothing fancy. And besides, the trigger works fine.
>
> I guess somehow the new trigger, though saved and working, was not
> recognised by its name as a member of the collection by the intellisense
> until a reboot had happened.
>
> /gustav
>
>
> >>> accessd at shaw.ca 11-06-12 19:14 >>>
> Hi Gustav:
>
> Have set up all the permissions right?
>
> Jim
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav
> Brock
> Sent: Monday, June 11, 2012 4:10 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Trigger syntax
>
> Hi Mark
>
> The code below was the code generated by the SQL template which is created
> when you add a new trigger.
> As you know, when you run the SQL containing "CREATE ...", the trigger
> itself containing "ALTER ..." is created.
> A trigger for Insert already existed. It is named
> [dbo].[T_VariantsTable_ITrig].
> That's why I chose the name [dbo].[T_VariantsTable_UTrig] for my Update
> trigger.
>
> However, this morning - after my workstation had been closed down for the
> weekend - the issue has vanished. So, for some reason, the new trigger
> hadn't been saved in full - or what? I really did logout and login again
> several times and refreshed "everything". But no reboot. Is that really
> needed?
>
> /gustav
>
>
> >>> marklbreen at gmail.com 11-06-12 10:33 >>>
> Hello Gustav
>
> I just copied your code into SSMS, change the two table names and it
> worked.  It created a trigger on a db here locally on my machine.
>
> The only thing I had to do was change the Alter to a Create.  Would that be
> your problem?
>
> If not, the only other thought that comes to mind is the PPT database the
> one with the table to hold the trigger.  IOW, does the db and table exist?
>
> Sorry I could not help more,
>
> Mark
>
>
> On 8 June 2012 14:08, Gustav Brock <gustav at cactus.dk> wrote:
>
> > Hi all
> >
> > I'm new to triggers but I've managed to create this which works as
> > intended:
> >
> > USE [PPT]
> > GO
> > /****** Object:  Trigger [dbo].[T_VariantsTable_UTrig]    Script Date:
> > 06/08/2012 14:45:17 ******/
> > SET ANSI_NULLS ON
> > GO
> > SET QUOTED_IDENTIFIER ON
> > GO
> > -- =============================================================
> > -- Author:              Gustav Brock
> > -- Create date: 2012-06-08
> > -- Description: Update field Created to time of update of record
> > -- =============================================================
> > ALTER TRIGGER [dbo].[T_VariantsTable_UTrig]
> >   ON [dbo].[VariantsTable]
> >   FOR UPDATE
> > AS
> > BEGIN
> >    -- SET NOCOUNT ON added to prevent extra result sets from
> >    -- interfering with SELECT statements.
> >    SET NOCOUNT ON;
> >
> >    -- Insert statements for trigger here
> >    DECLARE @id INT
> >    DECLARE @now DATETIME
> >
> >    -- Retrieve ID of changed record.
> >    SELECT @id = (SELECT ID FROM deleted)
> >    -- Get current time without milliseconds.
> >    SELECT @now = DATEADD(SECOND, DATEDIFF(SECOND, '20000101', GETDATE()),
> > '20000101')
> >
> >    UPDATE VariantsTable
> >    SET Changed = @now
> >    WHERE ID = @id
> > END
> >
> > However, when I open this in SMMS, this line has the last part red
> > underlined:
> >
> > ALTER TRIGGER [dbo].[T_VariantsTable_UTrig]
> >
> > stating: Invalid object name 'dbo.T_VariantsTable_UTrig'
> >
> > Why is that?
> > I have another trigger created by the Access upsize wizard with similar
> > code:
> >
> > ALTER TRIGGER [dbo].[T_VariantsTable_ITrig]
> >
> > and nothing is underlined here.
> >
> > /gustav