Professional Web Applications Themes

CREATE TRIGGER [testtrigger] ON * - Microsoft SQL / MS SQL Server

Is it possible to do something like CREATE TRIGGER [testtrigger] ON * FOR DELETE AS .... or will I have to do a trigger for every table? Marc...

  1. #1

    Default CREATE TRIGGER [testtrigger] ON *

    Is it possible to do something like
    CREATE TRIGGER [testtrigger] ON *

    FOR DELETE
    AS

    ....



    or will I have to do a trigger for every table?



    Marc


    Marc Fauser Guest

  2. #2

    Default Re: CREATE TRIGGER [testtrigger] ON *

    No, in T-SQL, '*' can be used only in SELECT statements & COUNT(*) function.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  3. #3

    Default Re: CREATE TRIGGER [testtrigger] ON *

    Anith Sen <anithbizdatasolutions.com> wrote:
    > No, in T-SQL, '*' can be used only in SELECT statements & COUNT(*)
    > function.
    Now I know that I don't have to search anymore for a small solution.
    Thank you. I will try to create all triggers with sp_MSforeachtable

    Marc


    Marc Fauser Guest

  4. #4

    Default Re: CREATE TRIGGER [testtrigger] ON *

    Actually, the script Dinesh posted is better, for two reasons... (1) it
    doesn't use an undoented proc, and (2) it just generates a script, which
    you can inspect before executing.

    IMHO.




    "Marc Fauser" <marc.fauser.N.O.S.P.A.Mfauser-ag.com> wrote in message
    news:O2aIPuwRDHA.2316tk2msftngp13.phx.gbl...
    > Anith Sen <anithbizdatasolutions.com> wrote:
    >
    > > No, in T-SQL, '*' can be used only in SELECT statements & COUNT(*)
    > > function.
    >
    > Now I know that I don't have to search anymore for a small solution.
    > Thank you. I will try to create all triggers with sp_MSforeachtable
    >
    > Marc
    >
    >

    Aaron Bertrand - MVP Guest

  5. #5

    Default Re: CREATE TRIGGER [testtrigger] ON *

    Dinesh.T.K <tkdineshnospam.mail.tkdinesh.com> wrote:
    > SELECT 'CREATE TRIGGER [testtrigger] ON '+table_name+char(13)+'FOR
    > DELETE'+char(13)+'AS'+ ' <rest of the body>'+char(13)+'GO'
    > FROM INFORMATION_SCHEMA.TABLES
    > WHERE table_type = 'BASE TABLE'
    > AND table_schema='dbo'
    >
    > This would generate a series of CREATE TRIGGER .. commands for all the
    > tables for that owner.You can then copy this code and execute it .
    Great. It worked perfectly.

    No I have another problem
    CREATE TRIGGER [testtrigger] ON table1 for UPDATE, DELETE AS ....

    Can you tell me how to get the information if the trigger was triggered by
    an
    update or by delete?


    Thank you very much.

    Marc


    Marc Fauser Guest

  6. #6

    Default Re: CREATE TRIGGER [testtrigger] ON *

    Marc,

    May be there is some easy way or may be not.Iam a bit held with some other
    work, so I can only give some hints for you to test if there is some way:

    1. Look at the logical tables 'inserted'and 'deleted' used in the scope of
    execution of triggers.When a row is updated, the previous value will be in
    'deleted' and the new value in 'inserted'.When a row is deleted, the value
    would be in 'deleted'.
    2. Take a look at OBJECTPROPERTY in BooksOnLine.It talks about some
    properties related to triggers.

    Please report back if you found a way ..meanwhile I will work on it and
    update this thread.


    --
    Dinesh.
    SQL Server FAQ at
    [url]http://www.tkdinesh.com[/url]

    "Marc Fauser" <marc.fauser.N.O.S.P.A.Mfauser-ag.com> wrote in message
    news:%23kn0mexRDHA.3236TK2MSFTNGP10.phx.gbl...
    > Dinesh.T.K <tkdineshnospam.mail.tkdinesh.com> wrote:
    >
    > > SELECT 'CREATE TRIGGER [testtrigger] ON '+table_name+char(13)+'FOR
    > > DELETE'+char(13)+'AS'+ ' <rest of the body>'+char(13)+'GO'
    > > FROM INFORMATION_SCHEMA.TABLES
    > > WHERE table_type = 'BASE TABLE'
    > > AND table_schema='dbo'
    > >
    > > This would generate a series of CREATE TRIGGER .. commands for all the
    > > tables for that owner.You can then copy this code and execute it .
    >
    > Great. It worked perfectly.
    >
    > No I have another problem
    > CREATE TRIGGER [testtrigger] ON table1 for UPDATE, DELETE AS ....
    >
    > Can you tell me how to get the information if the trigger was triggered by
    > an
    > update or by delete?
    >
    >
    > Thank you very much.
    >
    > Marc
    >
    >

    Dinesh.T.K Guest

  7. #7

    Default Re: CREATE TRIGGER [testtrigger] ON *

    "Marc Fauser" <marc.fauser.N.O.S.P.A.Mfauser-ag.com> wrote in message
    news:%23kn0mexRDHA.3236TK2MSFTNGP10.phx.gbl...
    > No I have another problem
    > CREATE TRIGGER [testtrigger] ON table1 for UPDATE, DELETE AS ....
    >
    > Can you tell me how to get the information if the trigger was triggered by
    > an
    > update or by delete?
    If triggered by a delete, the DELETED table will contain rows and the
    INSERTED table will not.
    If triggered by an update, DELETED and INSERTED will both contain rows.

    Steve


    Steve Troxell Guest

  8. #8

    Default Re: CREATE TRIGGER [testtrigger] ON *

    Use the following logic,

    If not exists (select * from inserted) --inserted virtual table does not
    exist
    Print 'Delete operation'
    else
    Print 'Update operation'

    "Dinesh.T.K" <tkdineshnospam.mail.tkdinesh.com> wrote in message
    news:OyToEWyRDHA.2148TK2MSFTNGP12.phx.gbl...
    > Marc,
    >
    > May be there is some easy way or may be not.Iam a bit held with some other
    > work, so I can only give some hints for you to test if there is some way:
    >
    > 1. Look at the logical tables 'inserted'and 'deleted' used in the scope of
    > execution of triggers.When a row is updated, the previous value will be in
    > 'deleted' and the new value in 'inserted'.When a row is deleted, the value
    > would be in 'deleted'.
    > 2. Take a look at OBJECTPROPERTY in BooksOnLine.It talks about some
    > properties related to triggers.
    >
    > Please report back if you found a way ..meanwhile I will work on it and
    > update this thread.
    >
    >
    > --
    > Dinesh.
    > SQL Server FAQ at
    > [url]http://www.tkdinesh.com[/url]
    >
    > "Marc Fauser" <marc.fauser.N.O.S.P.A.Mfauser-ag.com> wrote in message
    > news:%23kn0mexRDHA.3236TK2MSFTNGP10.phx.gbl...
    > > Dinesh.T.K <tkdineshnospam.mail.tkdinesh.com> wrote:
    > >
    > > > SELECT 'CREATE TRIGGER [testtrigger] ON '+table_name+char(13)+'FOR
    > > > DELETE'+char(13)+'AS'+ ' <rest of the body>'+char(13)+'GO'
    > > > FROM INFORMATION_SCHEMA.TABLES
    > > > WHERE table_type = 'BASE TABLE'
    > > > AND table_schema='dbo'
    > > >
    > > > This would generate a series of CREATE TRIGGER .. commands for all the
    > > > tables for that owner.You can then copy this code and execute it .
    > >
    > > Great. It worked perfectly.
    > >
    > > No I have another problem
    > > CREATE TRIGGER [testtrigger] ON table1 for UPDATE, DELETE AS ....
    > >
    > > Can you tell me how to get the information if the trigger was triggered
    by
    > > an
    > > update or by delete?
    > >
    > >
    > > Thank you very much.
    > >
    > > Marc
    > >
    > >
    >
    >

    Prasad Koukuntla Guest

  9. #9

    Default Re: CREATE TRIGGER [testtrigger] ON *

    > Use the following logic,
    >
    > If not exists (select * from inserted) --inserted virtual table does not
    > exist
    > Print 'Delete operation'
    > else
    > Print 'Update operation'
    Ooh, clever, thanks Prasad...


    Aaron Bertrand - MVP Guest

  10. #10

    Default Re: CREATE TRIGGER [testtrigger] ON *

    Prasad Koukuntla <prasad.koukuntlascapromo.com> wrote:
    > Use the following logic,
    >
    > If not exists (select * from inserted) --inserted virtual table does
    > not exist
    > Print 'Delete operation'
    > else
    > Print 'Update operation'
    Great solution. I like it.

    Marc


    Marc Fauser Guest

Similar Threads

  1. XML.TRIGGER
    By Dan-C in forum Macromedia Flash Data Integration
    Replies: 1
    Last Post: February 28th, 03:55 PM
  2. Trigger
    By James in forum ASP Database
    Replies: 1
    Last Post: August 6th, 01:05 AM
  3. CREATE TRIGGER
    By Stephen in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 20th, 07:56 AM
  4. Trigger Help
    By Mike in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 9th, 07:28 PM
  5. Trigger key
    By Elisabeth Smith in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 09:22 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139