Very Computer

Hello,

What are the difference between "FOR update" trigger and
"AFTER Update" Triggers?

The reason I am asking is because I cannot figure out how
to solve the below problems.

-- DDL

Create Trigger dbo.tr_modifyDate_Child_i ON dbo.Child
 For Update  AS
 BEGIN
     UPDATE Child
     SET modifyDate = GetDate()
     WHERE ParentId in (SELECT ParentId FROM INSERTED)
     AND ChildId in (SELECT ChildId FROM INSERTED)
 END

Create Trigger dbo.tr_modifyDate_Child_i ON dbo.Child
 For Insert AS
 BEGIN
     UPDATE Child
     SET CreateDate = GetDate()
     WHERE ParentId in (SELECT ParentId FROM INSERTED)
     AND ChildId in (SELECT ChildId FROM INSERTED)
 END

--drop table [dbo].[CHILD]
GO

CREATE TABLE [dbo].[CHILD] (
        [ParentID] [int] NOT NULL ,
        [ChildID] [int] NOT NULL ,
        [FirstName] [varchar] (50) NOT NULL ,
        [LastName] [varchar] (50) NOT NULL ,
        [ModifyDate] [datetime] NOT NULL ,
        [CreateDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CHILD] WITH NOCHECK ADD
        CONSTRAINT [PK_CHILD] PRIMARY KEY  CLUSTERED
        (
                [ParentID],
                [ChildID]
        )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[CHILD] ADD
        CONSTRAINT [DF_CHILD_ChildID] DEFAULT (0) FOR
[ChildID]
GO

--These triggers work fine, until I implement this:

CREATE Trigger dbo.tr_ChildID_Child_i ON dbo.Child  --
drop trigger tr_RequestID_Request_i
 FOR  Insert   AS
 BEGIN

     UPDATE Child
     SET ChildID = (Select max(ChildID) from Child as mV
                      where mV.ParentID = Request.ParentID
                      ) + 1
     From Child Inner Join Inserted
     On Child.ParentID = Inserted.ParentID
     Where Child.ChildID = 0

 END

--Thanks.

Description of the problems:

1. The modifyDate starts to get modified whenver the
ParentID gets changed, so there may be multiple modifyDate
get modified even there is only one ParentID + ChildID
gets changed.

2. The createDate does not work at work. Even with adding
the default GetDate(),the createDate just does not getDate
any more.

Any clue?

Thanks in advance.

Quote:>-----Original Message-----
>Hello,

>What are the difference between "FOR update" trigger and
>"AFTER Update" Triggers?

>The reason I am asking is because I cannot figure out how
>to solve the below problems.

>-- DDL

>Create Trigger dbo.tr_modifyDate_Child_i ON dbo.Child
> For Update  AS
> BEGIN
>     UPDATE Child
>     SET modifyDate = GetDate()
>     WHERE ParentId in (SELECT ParentId FROM INSERTED)
>     AND ChildId in (SELECT ChildId FROM INSERTED)
> END

>Create Trigger dbo.tr_modifyDate_Child_i ON dbo.Child
> For Insert AS
> BEGIN
>     UPDATE Child
>     SET CreateDate = GetDate()
>     WHERE ParentId in (SELECT ParentId FROM INSERTED)
>     AND ChildId in (SELECT ChildId FROM INSERTED)
> END

>--drop table [dbo].[CHILD]
>GO

>CREATE TABLE [dbo].[CHILD] (
>    [ParentID] [int] NOT NULL ,
>    [ChildID] [int] NOT NULL ,
>    [FirstName] [varchar] (50) NOT NULL ,
>    [LastName] [varchar] (50) NOT NULL ,
>    [ModifyDate] [datetime] NOT NULL ,
>    [CreateDate] [datetime] NOT NULL
>) ON [PRIMARY]
>GO

>ALTER TABLE [dbo].[CHILD] WITH NOCHECK ADD
>    CONSTRAINT [PK_CHILD] PRIMARY KEY  CLUSTERED
>    (
>            [ParentID],
>            [ChildID]
>    )  ON [PRIMARY]
>GO

>ALTER TABLE [dbo].[CHILD] ADD
>    CONSTRAINT [DF_CHILD_ChildID] DEFAULT (0) FOR
>[ChildID]
>GO

>--These triggers work fine, until I implement this:

>CREATE Trigger dbo.tr_ChildID_Child_i ON dbo.Child  --
>drop trigger tr_RequestID_Request_i
> FOR  Insert   AS
> BEGIN

>     UPDATE Child
>     SET ChildID = (Select max(ChildID) from Child as mV
>                  where mV.ParentID = Request.ParentID
>                  ) + 1
>     From Child Inner Join Inserted
>     On Child.ParentID = Inserted.ParentID
>     Where Child.ChildID = 0

> END

>--Thanks.

>.

Hi
You need to change the triggers and add defaults
first:

ALTER TABLE dbo.Child ADD
    CONSTRAINT DF_Child_CreateDate DEFAULT(GETDATE()) FOR CreateDate
GO
to replace trigger on insert in child
for update replace old by :

Quote:> Create Trigger dbo.tr_modifyDate_Child_i ON dbo.Child

  For Update  AS
  BEGIN

      UPDATE c
      SET modifyDate = GetDate()
         FROM Child c
                          JOIN INSERTED i --resolve issue (1)
                            ON ParentId = ParentId and ChildId = ChildId
  END

and new like :
 CREATE Trigger dbo.tr_ChildID_Child_i ON dbo.Child  --
-- drop trigger tr_RequestID_Request_i
  FOR  Insert   AS
 BEGIN

      UPDATE c
      SET ChildID = (Select max(ChildID) from Child mV
       where mV.ParentID = i.ParentID ) + 1
      From Child c Join Inserted i
      On c.ParentID = i.ParentID and c.ChildID = i.ChildID
      Where i.ChildID = 0

  END

Quote:> Hello,

> What are the difference between "FOR update" trigger and
> "AFTER Update" Triggers?

> The reason I am asking is because I cannot figure out how
> to solve the below problems.

> -- DDL

> Create Trigger dbo.tr_modifyDate_Child_i ON dbo.Child
>  For Update  AS
>  BEGIN
>      UPDATE Child
>      SET modifyDate = GetDate()
>      WHERE ParentId in (SELECT ParentId FROM INSERTED)
>      AND ChildId in (SELECT ChildId FROM INSERTED)
>  END

> Create Trigger dbo.tr_modifyDate_Child_i ON dbo.Child
>  For Insert AS
>  BEGIN
>      UPDATE Child
>      SET CreateDate = GetDate()
>      WHERE ParentId in (SELECT ParentId FROM INSERTED)
>      AND ChildId in (SELECT ChildId FROM INSERTED)
>  END

> --drop table [dbo].[CHILD]
> GO

> CREATE TABLE [dbo].[CHILD] (
> [ParentID] [int] NOT NULL ,
> [ChildID] [int] NOT NULL ,
> [FirstName] [varchar] (50) NOT NULL ,
> [LastName] [varchar] (50) NOT NULL ,
> [ModifyDate] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[CHILD] WITH NOCHECK ADD
> CONSTRAINT [PK_CHILD] PRIMARY KEY  CLUSTERED
> (
> [ParentID],
> [ChildID]
> )  ON [PRIMARY]
> GO

> ALTER TABLE [dbo].[CHILD] ADD
> CONSTRAINT [DF_CHILD_ChildID] DEFAULT (0) FOR
> [ChildID]
> GO

> --These triggers work fine, until I implement this:

> CREATE Trigger dbo.tr_ChildID_Child_i ON dbo.Child  --
> drop trigger tr_RequestID_Request_i
>  FOR  Insert   AS
>  BEGIN

>      UPDATE Child
>      SET ChildID = (Select max(ChildID) from Child as mV
>       where mV.ParentID = Request.ParentID
>       ) + 1
>      From Child Inner Join Inserted
>      On Child.ParentID = Inserted.ParentID
>      Where Child.ChildID = 0

>  END

> --Thanks.