Use of triggers in Sqlserver - Microsoft SQL Server

142 100+

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

Subscribe Post Reply

1377

debasisdas

8,127

Expert 4TB

Please find the details of triggers in sql server here .

Feb 6 '08 #2

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_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

142

100+

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

142

100+

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

Similar topics

SQL Triggers for Auditing

by: Keith | last post by:

Not sure if anyone in here knows the answer to this, but I asked in a SQL group and haven't had a suitable answer and since the front end app is ASP I though I'd give here a try. I am trying to...

ASP / Active Server Pages

statement level vs. row level triggers

by: Allan Hart | last post by:

Hi.. I'd very much appreciate it if someone would tell me how to translate a statement level trigger written in Oracle to its equivalent (if there is one) in MS SQL Server. Ditto for a row...

Microsoft SQL Server

Using triggers

by: Sjaak van Esdonk | last post by:

Hi all, i'm trying to find a solution for the following problem: I have two different database called A and B. On database A runs an application call AA. Some information from the database A...

Microsoft SQL Server

Why the memory usage of SqlServer doesn't go down

by: Jenniflower | last post by:

Hi Gurus, Our system is using SqlServer 2005 on XP.( On my machine,only this application access SQLServer.) The sqlserver memory is configured to 128MB (Min)~512 MB(Max) After our system get...

Microsoft SQL Server

Exceptions in Triggers MYSQL 5.0

by: Olimpio | last post by:

Hi, Someone knows about. How can i create exceptions or display messages in triggers, as the commands "raiserror" and "Print" in sqlserver ? Thanks, Olimpio.

MySQL Database

Triggers and Flag bit

by: Sharif Islam | last post by:

I have a perl script (uses sql query) that will run if there is any update in the row. Currently, I am a trigger that sets the DateModified field with getdate() Then my perl script looks for...

Microsoft SQL Server

Using Database triggers in a .net application

by: tswapnareddy | last post by:

Hello, this is swapna. I dont know about calling trigger into .net procedure. i create the trigger in sqlserver.after how to call the trigger into .net application Please help me how to do...

.NET Framework

converting DB2 triggers to Oracle

by: --CELKO-- | last post by:

I need to convert a bunch of DB2 triggers to Oracle. Is there any kind of tools for this?

DB2 Database

Why do I have to manually make sequences and triggers for primary keys?

by: BiffMaGriff | last post by:

Hi All, I'm an experienced MS SQL guy and I'm learning Oracle for my new job. I've been asking around and apparently no-one has ever used a tool that would automatically create sequences for a...

Oracle Database

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice