• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1366
  • Last Modified:

Trigger when insert

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
caoimhincryan
Asked:
caoimhincryan
  • 5
  • 3
1 Solution
 
johnaryanCommented:
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
 
caoimhincryanAuthor Commented:
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
 
johnaryanCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
caoimhincryanAuthor Commented:
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
 
caoimhincryanAuthor Commented:
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
 
caoimhincryanAuthor Commented:
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
 
johnaryanCommented:
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
 
caoimhincryanAuthor Commented:
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
 
Daniel WilsonCommented:
>>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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now