Solved

Trigger when insert

Posted on 2008-10-02
9
1,358 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 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