[dba-SQLServer] Trigger syntax

Hello Gustav,

Oh yes, the intellisense can cause a lot of hassle chasing a syntax error
that does not exist.

Do you use SQL Prompt by Redgate?

Mark


On 12 June 2012 09:39, 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
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>