Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Trigger when insert

Posted on 2008-10-02
9
1,359 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Finding Events logs for IIS website that restarts 2 14
abstract class C# 1 31
Where is this file? 3 25
Need syntax help building hyperlink 2 27
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.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

829 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