.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

trigger in sql server

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :ASP.Net

im new to trigger concept

when i insert data in a table [DSR_OUT_PRO] data get enter in another table ie [trg_Insert_DSR_OUT_PRO] but 2 times..

Is it ok, or something is wrong..

View Complete Post

More Related Resource Links

SQL Server 2008, what is the difference between (for/after) definition for trigger?

Hello. The difference is not clear for me. can someone explain it more carefully pls? as said: FOR | AFTER AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires. AFTER is the default when FOR is the only keyword specified. So what is the difference between for insert and after insert?

How to create/trigger a server-only script

Background:  I have a site that sends a good bit of emails, some one at a time, and some larger groups of admin emails.  I'm running on a shared hosting service.  The mailserver is horribly slow, so any script that sends a single email can take as much as 15 seconds to execute (and I can't seem to set Response.BufferOutput  to false, so the whole page has to wait the entire duration).  On the bulk emailings, it can take minutes for that to run.I want to create a "mail queue" of my own... I want any script that sends an email to, instead, write a row to a db table containing the necessary email info, which will let the script get on with its business quickly.  Then, I want to create a script that runs on the server only, and have it check the table for emails, send them, and remove them from the table.I know I can have the "mailsender" script run as a scheduled task, and it would thereby run on the server only.  But I would prefer to have the initial script somehow "trigger" the mailsender script.So, QUESTION:  Is there a way in asp.net to create a script that will execute solely on the server, and call that script from another page, so that the calling page doesn't wait for the server script to execute?Thanks!

How to write a trigger when given a condition in SQL SERVER 2005?


Hi Guys,
I am using SQL SERVER 2005.

I know how to write a trigger for a basic insert or update or delete. But here in my case I have to use the below condition and create a trigger.

I havent used any trigger till now in my job. So I am facing a slight problem in understanding and writing the code.....
I got the following requirement to create a new trigger for the following scenario :

The user (john) should be notified when the following happens :

Any transaction entered to account 100 which is not a ABN,CCH,or TTW
Any transaction entered to account 189 which is not a KHD

Description of fields :
100 and 189 are values for AccountCode Column in Account Table
ABN,CCH,TTW and KHD are values for CompanyTypeCode Column in Company Table.
I need to create new trigger on DLT_Data table

In a trigger can up insert data into a table located on a linked server?


EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'False',@locallogin=NULL,@rmtuser=N'lnklogin',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'query timeout', @optvalue=N'0'

SQL Server 2008 Error creating trigger


I am working in sqlserver 2008 with SP1.  DB mail is setup and works with no issues.  When creating a trigger am getting the following error:

 Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42 profile name is not valid

Not sure where this error is comming from.  Thanks in advance for any help.  The code is below: 

CREATE Trigger TR_BLCargo ON BLCargo


SQL SErver 2008 Merge Replication: Alter Trigger cause syntax error on subscriber site


I have 2 clustered instances running on SQL Server 2008 SE-64 patch level 10.0.2531.0. These is one DB on these 2 instances (compatibility_level=80)under merge replication. now I need to change one trigger to add "NOT FOR REPLICATION". One publisher site all is ok but on subscriber site it causes Error 102 Severity 15 State 1 Incorrect Syntax near 'dbo'.

After tracing the error in profiler, I captured the incorrect syntax as below:

exec('ALTER TRIGGER [dbo].[trgBusinessEntityAllocationUpdate] on [dbo].[BusinessEntityAllocation] 

obviously, there is an duplicated part of object name. but the script was generated by replication engine. How could it happened? can anyone help?



George the DBA

Notify jquery from server (trigger an asynchronous client side event from the server).



I want to have a code that would establish connection from the server to the jQuery client, to update the front page of the website, when some new data is added to the DB. I of course could have an ajax call from the client on every second to check up the database for any changes, but I am just wondering if there is any way to establish a connection from the server side to the client.

The perfect solution would work like that:

- when the page is open, the client registers itself on the server for changes

- the server (knowing all the registered clients) prompts all of them when a change is done

- if any of the clients does not respond, the server removes the client from the registered list

- clients, update the page contents with the new data from the server

The question is if it is possible? Again, I could make all the clients reconnect to the server every second using ajax, however, this does not seem like a clean solution, and additionally it would put an unnecessary load on the server (e.q. 100 clients requesting update every second).

Thanks in advance,

Jozef A. Habdank

Data from the server subscription cannot trigger table trigger


Publisher  Server A  (Job  table) no trigger

Subscription Server B (Job table) has trigger

Data from the Server A subscription cannot trigger Server B Job table trigger

How to display a message in SQL Server 2008 inside a trigger?

I'm using a simple try-catch block inside a trigger. On catch, I rollback the trans but I also want to be able to display some sort of message to myself (the error) since I'm having a hard time debugging the errors in my triggers. It is a after delete, update trigger and whenever I delete a record, the message displayed is transaction ended in a trigger and since there are multiple try-catch blocks, i don't know which one threw the catch and why it did.

how to send message to external message queue in a trigger of sql server ?


i write a code to send message to windows message queue,but when i try to add it to clr but failed for need to reference other dll like system.messaging.dll , and when i try to add this dll first, but it need other dll ,too. so i tired to add these dll, and want to know how to
send message to message queue, maybe i'm wrong , maybe there's better way. so ,i hope someone can help me to solve this.
code :
 public class MSGQSpiderTrigger
        public void ExportToMSG()
            SqlTriggerContext sqlTrigger = SqlContext.TriggerContext;
            SqlDataReader reader;
            SqlCommand command;
            MSGDocInfo doc = new MSGDocInfo();
            if (sqlTrigger.TriggerAction == TriggerAction.Insert)
                using (SqlConnection connection  = new SqlConnection(@"context connection=true"))
                    command = new SqlCommand(@"SELECT * FROM INSERTED;", connection);
                    reader = command.ExecuteReader();
                   //.... read my data


                  //then send to windows message queue.  

MySQL trigger send a request/call ASP.NET server


I am using MySQL 5.

Is there any possibility that MYSQL trigger has capability to communicate with a .NET service/ASP.NET server?

What I want to do is if there is any update of a table record (eg. employee) sends a "notification" to a server so that latest data will be retrieved by the ASP.NET server.

Trigger for backup after SQL Server Start


Hi there.

How can I create a trigger which backs up one database after every sql server start?

How to do DDL Trigger to SQL Server 2000


I'd like to block drop database, but i can't do this with the sql login.

I tried create ddl trigger on SQL 2000 but i had some erros. With SQL 2005, 2008 and 2008R2 no problem!

CREATE TRIGGER Triggerpreventdropdatabase
ON all server
SET @db = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)'))
SET @db = REPLACE(@db,'[','');
SET @db = REPLACE(@db,']','');
PRINT ('Drop database isn't permited!')

Some erros:

Msg 156, Level 15, State 1, Procedure Triggerpreventdropdatabase, Line 2

Incorrect syntax near the keyword 'ALL'.

Msg 195, Level 15, State 10, Procedure Triggerpreventdropdatabase, Line 7

'EVENTDATA' is not a recognized function name.


Help me please!!!


How use trigger in SQL server 2005


Hi . I have a lots of trigger in SQL server 2005

create table Product
 IDproduct char(10) primary key not null,
 Nameproduct nvarchar(10) not null,
 Prices money not null,
 inventory int not null,
create table Employee
 IDemployee char(10) primary key not null,
 Nameemployee nvarchar(10) not null,
create table Customer
 IDcustomer char(10) primary 

call JavaScript - jQuery code from ASP.NET Server-Side

jQuery got so close to me lately that I can see myself adding the scripts to my project almost unconsciously. The thing is, jQuery is very useful for me, in almost all situations and it has been a do or die enhancement for all my project since I first put my hands on it.

Of course while using it, you encounter few situations which need a bit of research to solve, mostly when you are trying to combine it with some other technologies like: UpdatePanels and ASP.NET Ajax. For instance there are many situations when I would like to run some jQuery magic based on some decision that I make on the server side.

Making Ajax Callbacks to the Server Using jQuery with ASP.NET

In the first part of this article series I introduced jQuery's functionality and how it provides a rich client side programming model. This time around I'll expand on these concepts and show you how you can use jQuery in combination with ASP.NET using it as an AJAX backend to retrieve data. I'll also discuss how you can create ASP.NET controls and otherwise interact with jQuery content from ASP.NET pages in WebForms.

jQuery is just a JavaScript library so it will work seamlessly with ASP.NET both from page code as well as through backend driven code using the Page.ClientScript object or ScriptManager. You can use jQuery on its own as a client side and Ajax library that communicates with ASP.NET or you can use jQuery in combination with ASP.NET AJAX. The two actually complement each other quite well as jQuery provides functionality that the ASP.NET AJAX library does not and vice versa. For the most part the interaction between the two libraries is trouble free except for a few very rare edge cases.

MS SQL Server: Disconnect Users From Database - Kill User Session

If you ever wanted to restore your database from a SQL backup file (.bak), but there are still users connected to your database, the backup operation will fail causing the error: Exclusive access could not be obtained because the database is in use.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend