Solved

Trigger when insert

Posted on 2008-10-02
9
1,360 Views
Last Modified: 2013-12-17
Hi all,

I am new to triggers and am looking for an example to get me started. Im looking for an example that uses database BEI and table Derap.

Table Derap has a field called Type.

I want a trigger that when a new entry is entered into the table Derap to check to see is the word "Important" inserted into field Type.

Can anyone help?
0
Comment
Question by:caoimhincryan
  • 5
  • 3
9 Comments
 
LVL 5

Expert Comment

by:johnaryan
ID: 22622514
If you have SQL Server 2005 installed the you should have "SQL-Server Books On-Line" in the Start>Programs>Sql Server 2005>Documentation and Tutorials.

It's full of examples on how to use Triggers and everything else SQL has to offer.
0
 

Author Comment

by:caoimhincryan
ID: 22622728
Can you get a trigger to say execute a program? Say my triggers finds that "Important" is entered to field called "Type"..can you det it to execute a vb.net exe?
0
 
LVL 5

Expert Comment

by:johnaryan
ID: 22622823
Yes, there are permissions that are required, but easily over come.

in Books OnLine look up General Extended Stored Procedures there are items like:
xp_cmdshell that allow you to run exe's
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:caoimhincryan
ID: 22623086
I follwed these steps to to allow xp_cmdshell

http://www.mssqltips.com/tip.asp?tip=1020

However I can parse my trigger but when i got to execute it, it just keeps going saying executing query in the bottom left corner and wont stop.

Any ideas? this is my trigger..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[reminder]
ON [dbo].[AlertTypeTable]
FOR INSERT, UPDATE, DELETE
AS
  BEGIN
DECLARE @cmd VARCHAR(2000)

 
  SET @cmd = 'START C:\Program Files\...\...\Receive.exe'

  EXEC master.dbo.xp_cmdshell @cmd


END
0
 

Author Comment

by:caoimhincryan
ID: 22624822
I have updates my trigger, it is now:

ALTER TRIGGER [dbo].[InsertAlertType]
ON [dbo].[AlertTypes]
AFTER INSERT, UPDATE
AS
BEGIN


DECLARE @col2 INT
DECLARE @cmd VARCHAR(2000)


set @col2 = (SELECT AlertType FROM inserted)


IF @col2 = 3

      SET @cmd = 'START C:\Program Files\Default Company Name\UDP Receive Setup\Receive'
      EXEC master.dbo.xp_cmdshell @cmd


END
0
 

Author Comment

by:caoimhincryan
ID: 22685091
When i try the below it works fine:

exec master.dbo.xp_cmdshell 'copy c:\Test.txt c:\BackupTest.txt'

However when I try the below it doesnt execute, it times out:

SET @cmd = '"C:\Program Files\...\...\Receive.exe"'
 
EXEC master.dbo.xp_cmdshell @cmd

Any ideas? Am i approaching this right? Is there a better way?
0
 
LVL 5

Expert Comment

by:johnaryan
ID: 22686545
I was looking at the BOL and it says:

1. The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.

2. xp_cmdshell operates synchronously. Control is not returned to the caller until the command-shell command is completed.

Does SQL server have permissions to execute .exe files? and does the Recieve.exe terminate/exit correctly?

0
 

Author Comment

by:caoimhincryan
ID: 22686784
It does have permissions i think.

Point to note is that while it says it is executing, i can see a receive.exe in the proces list in task manager. The user name for this process is SYSTEM. The form however doesnt load up. Do you think it has something to do with the username?
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 22939664
>>The form however doesnt load up.
You do NOT want to kick off a GUI process from a server process.  Whether that server is SQL Server, IIS, Task Scheduler -- kicking off something that requires user interaction, requires the user interaction in THAT user's logged in session.  
If that user's logged in session is not the one on the terminal, no one will see the GUI, the message box, etc.
A classic error of this sort is to put a message box in an application that could ALMOSt run silently ... then schedule it in Task Scheduler.  It reports that it's running .. but no one can see the message box, so it practically hangs.
You're running into the same thing a different way. To sum up:
Don't kick a GUI program from a server process.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Problem to Office 1 39
Updating a single record in forEach using C#/Entity 7 23
visual studio vb.net windows onkeyup 2 25
Header Font Size in Grid View 6 22
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question