Use of triggers in Sqlserver

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • mafaisal
    New Member
    • Sep 2007
    • 142
    #1

    Use of triggers in Sqlserver

    Hi experts

    I am Using Sql Server 2005

    Hw to Create & call of triggers in sqlserver

    I have Table salesMast,sales det,stock

    When after insertion in salesdet stock is minus & when cancel sales stock plus

    i cancel the sales by updating column sales =1

    This transaction hw to manipulate using triggers

    I have little knowledge

    Plz Give me Example

    Thanks in advance

    Faisal
    Tags: None
    • debasisdas
      Recognized Expert Expert
      • Dec 2006
      • 8119
      #2
      Please find the details of triggers in sql server here .

        Comment

        • prafullashimpi
          New Member
          • Feb 2008
          • 1
          #3
          Hi Faisal,


          If you expand Database, you will tablelist. Expand table, you will get list of folders like:

          -Columns
          -Keys
          -Constraints
          -Triggers

          etc..

          If you right click on Triggers folders, you can get option to create 'NewTrigger'
          select that
          then template you will get to create a new trigger, somewhat like this:

          [size=2][color=#008000]-- =============== =============== =============== ===

          -- Template generated from Template Explorer using:

          -- Create Trigger (New Menu).SQL

          -- Use the Specify Values for Template Parameters

          -- command (Ctrl-Shift-M) to fill in the parameter

          -- values below.

          -- See additional Create Trigger templates for more

          -- examples of different Trigger statements.

          -- This block of comments will not be included in

          -- the definition of the function.

          -- =============== =============== =============== ===

          [/color][/size][size=2][color=#0000ff]SET[/color][/size][size=2] [/size][size=2][color=#0000ff]ANSI_NULLS[/color][/size][size=2] [/size][size=2][color=#0000ff]ON

          [/color][/size][size=2]GO

          [/size][size=2][color=#0000ff]SET[/color][/size][size=2] [/size][size=2][color=#0000ff]QUOTED_IDENTIFI ER[/color][/size][size=2] [/size][size=2][color=#0000ff]ON

          [/color][/size][size=2]GO

          [/size][size=2][color=#008000]-- =============== =============== ===============

          -- Author: <Author,,Name >

          -- Create date: <Create Date,,>

          -- Description: <Description, ,>

          -- =============== =============== ===============

          [/color][/size][size=2][color=#0000ff]CREATE[/color][/size][size=2] [/size][size=2][color=#0000ff]TRIGGER[/color][/size][size=2] [/size][size=2][color=#808080]<[/color][/size][size=2]Schema_Name[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]sysname[/color][/size][size=2][color=#808080],[/color][/size][size=2] Schema_Name[/size][size=2][color=#808080]>.<[/color][/size][size=2]Trigger_Name[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]sysname[/color][/size][size=2][color=#808080],[/color][/size][size=2] Trigger_Name[/size][size=2][color=#808080]>[/color][/size][size=2]

          [/size][size=2][color=#0000ff]ON[/color][/size][size=2] [/size][size=2][color=#808080]<[/color][/size][size=2]Schema_Name[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]sysname[/color][/size][size=2][color=#808080],[/color][/size][size=2] Schema_Name[/size][size=2][color=#808080]>.<[/color][/size][size=2]Table_Name[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]sysname[/color][/size][size=2][color=#808080],[/color][/size][size=2] Table_Name[/size][size=2][color=#808080]>[/color][/size][size=2]

          AFTER [/size][size=2][color=#808080]<[/color][/size][size=2]Data_Modificati on_Statements[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]INSERT[/color][/size][size=2][color=#808080],[/color][/size][size=2][color=#0000ff]DELETE[/color][/size][size=2][color=#808080],[/color][/size][size=2][color=#0000ff]UPDATE[/color][/size][size=2][color=#808080]>

          [/color][/size][size=2][color=#0000ff]AS[/color][/size][size=2]

          [/size][size=2][color=#0000ff]BEGIN

          [/color][/size][size=2][/size][size=2][color=#008000]-- SET NOCOUNT ON added to prevent extra result sets from

          [/color][/size][size=2][/size][size=2][color=#008000]-- interfering with SELECT statements.

          [/color][/size][size=2][/size][size=2][color=#0000ff]SET[/color][/size][size=2] [/size][size=2][color=#0000ff]NOCOUNT[/color][/size][size=2] [/size][size=2][color=#0000ff]ON[/color][/size][size=2][color=#808080];

          [/color][/size][size=2][/size][size=2][color=#008000]-- Insert statements for trigger here

          [/color][/size][size=2][color=#0000ff]END

          [/color][/size][size=2]GO

          [/size]

          Here, you can write SQL Statmenets based on your condition.

          guideline: you need two triggers
          a. after Insert (To decrement)
          b. after Update (To Increment)
          - here you need to check whether order cancelled or not?


          I hope, this will help you, if not, reply me back i will try to write triggers for you.

          Regards,


          Prafulla

            Comment

            • mafaisal
              New Member
              • Sep 2007
              • 142
              #4
              Hi Prafulla,

              I will try for , thanx for ur Reply

              Originally posted by prafullashimpi
              Hi Faisal,


              If you expand Database, you will tablelist. Expand table, you will get list of folders like:

              -Columns
              -Keys
              -Constraints
              -Triggers

              etc..

              If you right click on Triggers folders, you can get option to create 'NewTrigger'
              select that
              then template you will get to create a new trigger, somewhat like this:

              [size=2][color=#008000]-- =============== =============== =============== ===

              -- Template generated from Template Explorer using:

              -- Create Trigger (New Menu).SQL

              -- Use the Specify Values for Template Parameters

              -- command (Ctrl-Shift-M) to fill in the parameter

              -- values below.

              -- See additional Create Trigger templates for more

              -- examples of different Trigger statements.

              -- This block of comments will not be included in

              -- the definition of the function.

              -- =============== =============== =============== ===

              [/color][/size][size=2][color=#0000ff]SET[/color][/size][size=2] [/size][size=2][color=#0000ff]ANSI_NULLS[/color][/size][size=2] [/size][size=2][color=#0000ff]ON

              [/color][/size][size=2]GO

              [/size][size=2][color=#0000ff]SET[/color][/size][size=2] [/size][size=2][color=#0000ff]QUOTED_IDENTIFI ER[/color][/size][size=2] [/size][size=2][color=#0000ff]ON

              [/color][/size][size=2]GO

              [/size][size=2][color=#008000]-- =============== =============== ===============

              -- Author: <Author,,Name >

              -- Create date: <Create Date,,>

              -- Description: <Description, ,>

              -- =============== =============== ===============

              [/color][/size][size=2][color=#0000ff]CREATE[/color][/size][size=2] [/size][size=2][color=#0000ff]TRIGGER[/color][/size][size=2] [/size][size=2][color=#808080]<[/color][/size][size=2]Schema_Name[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]sysname[/color][/size][size=2][color=#808080],[/color][/size][size=2] Schema_Name[/size][size=2][color=#808080]>.<[/color][/size][size=2]Trigger_Name[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]sysname[/color][/size][size=2][color=#808080],[/color][/size][size=2] Trigger_Name[/size][size=2][color=#808080]>[/color][/size][size=2]

              [/size][size=2][color=#0000ff]ON[/color][/size][size=2] [/size][size=2][color=#808080]<[/color][/size][size=2]Schema_Name[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]sysname[/color][/size][size=2][color=#808080],[/color][/size][size=2] Schema_Name[/size][size=2][color=#808080]>.<[/color][/size][size=2]Table_Name[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]sysname[/color][/size][size=2][color=#808080],[/color][/size][size=2] Table_Name[/size][size=2][color=#808080]>[/color][/size][size=2]

              AFTER [/size][size=2][color=#808080]<[/color][/size][size=2]Data_Modificati on_Statements[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]INSERT[/color][/size][size=2][color=#808080],[/color][/size][size=2][color=#0000ff]DELETE[/color][/size][size=2][color=#808080],[/color][/size][size=2][color=#0000ff]UPDATE[/color][/size][size=2][color=#808080]>

              [/color][/size][size=2][color=#0000ff]AS[/color][/size][size=2]

              [/size][size=2][color=#0000ff]BEGIN

              [/color][/size][size=2][/size][size=2][color=#008000]-- SET NOCOUNT ON added to prevent extra result sets from

              [/color][/size][size=2][/size][size=2][color=#008000]-- interfering with SELECT statements.

              [/color][/size][size=2][/size][size=2][color=#0000ff]SET[/color][/size][size=2] [/size][size=2][color=#0000ff]NOCOUNT[/color][/size][size=2] [/size][size=2][color=#0000ff]ON[/color][/size][size=2][color=#808080];

              [/color][/size][size=2][/size][size=2][color=#008000]-- Insert statements for trigger here

              [/color][/size][size=2][color=#0000ff]END

              [/color][/size][size=2]GO

              [/size]

              Here, you can write SQL Statmenets based on your condition.

              guideline: you need two triggers
              a. after Insert (To decrement)
              b. after Update (To Increment)
              - here you need to check whether order cancelled or not?


              I hope, this will help you, if not, reply me back i will try to write triggers for you.

              Regards,


              Prafulla

                Comment

                • mafaisal
                  New Member
                  • Sep 2007
                  • 142
                  #5
                  Hello
                  Plz Give an Eg of Triggers

                  Also How to Know when the Order is Cancelled, on Update or

                  Thanx in Advance

                  Originally posted by prafullashimpi
                  Hi Faisal,


                  If you expand Database, you will tablelist. Expand table, you will get list of folders like:

                  -Columns
                  -Keys
                  -Constraints
                  -Triggers

                  etc..

                  If you right click on Triggers folders, you can get option to create 'NewTrigger'
                  select that
                  then template you will get to create a new trigger, somewhat like this:

                  [size=2][color=#008000]-- =============== =============== =============== ===

                  -- Template generated from Template Explorer using:

                  -- Create Trigger (New Menu).SQL

                  -- Use the Specify Values for Template Parameters

                  -- command (Ctrl-Shift-M) to fill in the parameter

                  -- values below.

                  -- See additional Create Trigger templates for more

                  -- examples of different Trigger statements.

                  -- This block of comments will not be included in

                  -- the definition of the function.

                  -- =============== =============== =============== ===

                  [/color][/size][size=2][color=#0000ff]SET[/color][/size][size=2] [/size][size=2][color=#0000ff]ANSI_NULLS[/color][/size][size=2] [/size][size=2][color=#0000ff]ON

                  [/color][/size][size=2]GO

                  [/size][size=2][color=#0000ff]SET[/color][/size][size=2] [/size][size=2][color=#0000ff]QUOTED_IDENTIFI ER[/color][/size][size=2] [/size][size=2][color=#0000ff]ON

                  [/color][/size][size=2]GO

                  [/size][size=2][color=#008000]-- =============== =============== ===============

                  -- Author: <Author,,Name >

                  -- Create date: <Create Date,,>

                  -- Description: <Description, ,>

                  -- =============== =============== ===============

                  [/color][/size][size=2][color=#0000ff]CREATE[/color][/size][size=2] [/size][size=2][color=#0000ff]TRIGGER[/color][/size][size=2] [/size][size=2][color=#808080]<[/color][/size][size=2]Schema_Name[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]sysname[/color][/size][size=2][color=#808080],[/color][/size][size=2] Schema_Name[/size][size=2][color=#808080]>.<[/color][/size][size=2]Trigger_Name[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]sysname[/color][/size][size=2][color=#808080],[/color][/size][size=2] Trigger_Name[/size][size=2][color=#808080]>[/color][/size][size=2]

                  [/size][size=2][color=#0000ff]ON[/color][/size][size=2] [/size][size=2][color=#808080]<[/color][/size][size=2]Schema_Name[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]sysname[/color][/size][size=2][color=#808080],[/color][/size][size=2] Schema_Name[/size][size=2][color=#808080]>.<[/color][/size][size=2]Table_Name[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]sysname[/color][/size][size=2][color=#808080],[/color][/size][size=2] Table_Name[/size][size=2][color=#808080]>[/color][/size][size=2]

                  AFTER [/size][size=2][color=#808080]<[/color][/size][size=2]Data_Modificati on_Statements[/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#808080],[/color][/size][size=2] [/size][size=2][color=#0000ff]INSERT[/color][/size][size=2][color=#808080],[/color][/size][size=2][color=#0000ff]DELETE[/color][/size][size=2][color=#808080],[/color][/size][size=2][color=#0000ff]UPDATE[/color][/size][size=2][color=#808080]>

                  [/color][/size][size=2][color=#0000ff]AS[/color][/size][size=2]

                  [/size][size=2][color=#0000ff]BEGIN

                  [/color][/size][size=2][/size][size=2][color=#008000]-- SET NOCOUNT ON added to prevent extra result sets from

                  [/color][/size][size=2][/size][size=2][color=#008000]-- interfering with SELECT statements.

                  [/color][/size][size=2][/size][size=2][color=#0000ff]SET[/color][/size][size=2] [/size][size=2][color=#0000ff]NOCOUNT[/color][/size][size=2] [/size][size=2][color=#0000ff]ON[/color][/size][size=2][color=#808080];

                  [/color][/size][size=2][/size][size=2][color=#008000]-- Insert statements for trigger here

                  [/color][/size][size=2][color=#0000ff]END

                  [/color][/size][size=2]GO

                  [/size]

                  Here, you can write SQL Statmenets based on your condition.

                  guideline: you need two triggers
                  a. after Insert (To decrement)
                  b. after Update (To Increment)
                  - here you need to check whether order cancelled or not?


                  I hope, this will help you, if not, reply me back i will try to write triggers for you.

                  Regards,


                  Prafulla

                    Comment

                    Previous template Next
                    Working...