Results 1 to 7 of 7

Thread: Triggers

  1. #1
    Join Date
    Jan 2004
    Posts
    18

    Question Unanswered: Triggers

    Hi,

    I'm completely new to triggers and I have this problem. I would like to create a trigger that updates the "DateModified" column with the current Date/Time. When anyting in a row is updated or modified, I would like the Date/Time to be updated on that row only.

    Here is my code/pseudo code, abviously not working, anyone can help?

    CREATE TRIGGER tr1 ON [dbo].[PageInfo]
    FOR INSERT, UPDATE
    AS
    IF "current row" is UPDATED
    Begin
    update on PageInfo (current row)
    set DateModified = getdate() (on current row only)
    end

    thanks in advance

    Gazzou

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    drop table test
    go
    create table test(id int null,code varchar(10) null,updated datetime null)
    go
    alter trigger setupdated on test
    for insert,update
    as
    if update(id) or update(code)
    update test set updated=getdate()
    from inserted where test.id=inserted.id
    go
    insert test(id,code) values(1,'a')
    insert test(id,code) values(2,'b')
    insert test(id,code) values(3,'c')
    go
    select * from test

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ahhhh....I got sniped.....

    here you go anyway.....

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), col2 datetime, Col3 varchar(10))
    GO
    
    CREATE TRIGGER myTable99_tr on myTable99
    FOR INSERT,UPDATE
    AS
        UPDATE t SET Col2 = GetDate()
          FROM myTable99 t
    INNER JOIN inserted i 
            ON t.Col1 = i.Col1
    GO
    
    INSERT INTO myTable99(Col3) SELECT 'Brett'
    SELECT * FROM myTable99
    INSERT INTO myTable99(Col3) SELECT 'Gazzou'
    UPDATE myTable99 SET Col3 = 'X002548' WHERE Col1 = 1 
    SELECT * FROM myTable99
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Brett Kaiser
    ahhhh....I got sniped.....

    here you go anyway.....

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 int IDENTITY(1,1), col2 datetime, Col3 varchar(10))
    GO
    
    CREATE TRIGGER myTable99_tr on myTable99
    FOR INSERT,UPDATE
    AS
        UPDATE t SET Col2 = GetDate()
          FROM myTable99 t
    INNER JOIN inserted i 
            ON t.Col1 = i.Col1
    GO
    
    INSERT INTO myTable99(Col3) SELECT 'Brett'
    SELECT * FROM myTable99
    INSERT INTO myTable99(Col3) SELECT 'Gazzou'
    UPDATE myTable99 SET Col3 = 'X002548' WHERE Col1 = 1 
    SELECT * FROM myTable99
    
    DROP TABLE myTable99
    GO
    Sorry, Brett - it was my turn

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well....except it won't execute for him/her...got a slight prob...

    Code:
    create table test([id] int null,code varchar(10) null,updated datetime null)
    go
    create trigger setupdated on test
    for insert,update
    as
    if update([id]) or update(code) 
    update test set updated=getdate()
    from inserted where test.id=inserted.id 
    go
    insert test(id,code) values(1,'a')
    insert test(id,code) values(2,'b')
    insert test(id,code) values(3,'c')
    select * from test
    UPDATE test SET code = 'd' WHERE [id] = 3
    go
    select * from test
    GO
    
    DROP TABLE Test
    GO
    and why bother with the IF?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Brett Kaiser
    Well....except it won't execute for him/her...got a slight prob...

    Code:
    create table test([id] int null,code varchar(10) null,updated datetime null)
    go
    create trigger setupdated on test
    for insert,update
    as
    if update([id]) or update(code) 
    update test set updated=getdate()
    from inserted where test.id=inserted.id 
    go
    insert test(id,code) values(1,'a')
    insert test(id,code) values(2,'b')
    insert test(id,code) values(3,'c')
    select * from test
    UPDATE test SET code = 'd' WHERE [id] = 3
    go
    select * from test
    GO
    
    DROP TABLE Test
    GO
    and why bother with the IF?
    It was idea about do not allow (or ignore) updates for field with datetime of updating.

    create trigger setupdated on test
    for insert,update
    as
    if update(id) or update(code)
    update test set updated=getdate()
    from inserted where test.id=inserted.id
    else
    rollback tran
    go

  7. #7
    Join Date
    Jan 2004
    Posts
    18

    Cool

    Thanks you very much for the replies, it works well.

    Gazzou :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •