?
Solved

Trigger when insert

Posted on 2008-10-02
9
Medium Priority
?
1,363 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
[X]
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
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

800 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