By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,695 Members | 1,651 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,695 IT Pros & Developers. It's quick & easy.

Use of triggers in Sqlserver

mafaisal
100+
P: 142
Hi experts

I am Using Sql Server 2005

Hw to Create & call of triggers in sqlserver

I have Table salesMast,salesdet,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
Feb 6 '08 #1
Share this Question
Share on Google+
4 Replies


debasisdas
Expert 5K+
P: 8,127
Please find the details of triggers in sql server here .
Feb 6 '08 #2

P: 1
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_IDENTIFIER[/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_Modification_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
Feb 6 '08 #3

mafaisal
100+
P: 142
Hi Prafulla,

I will try for , thanx for ur Reply

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_IDENTIFIER[/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_Modification_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
Feb 7 '08 #4

mafaisal
100+
P: 142
Hello
Plz Give an Eg of Triggers

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

Thanx in Advance

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_IDENTIFIER[/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_Modification_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
Feb 7 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.