Creating a trigger for specified value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating a trigger for specified value

Author

Topic

fugazi
Starting Member

6 Posts

Posted - 2009-08-28 : 22:50:44

Hello everybody ! i'm happy to join your community. I'm a newbie for creating triggers on sql tables.

There is my problem :

I have a table containing values, those values are coming from a machine in my plant. I want to create a trigger telling me that a low value has been inserted in the table. That's why i want to create a trigger sending me a net send message. Unfortunately i'm not able to make it work !

CREATE TRIGGER [reject part] ON [dbo].[rPro_HardnessTester]
FOR INSERT
AS (select * from rpro_hardnesstester where hardnesstestervalue < '30')

EXEC master..xp_cmdshell 'net send mycomputer New EP1 rejected parts for hardness'

This trigger syntax is ok to me but it's not working. Can someone help me please ?

best regards,

Daniel

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-29 : 12:40:19

1st, your trigger should look more like this:


CREATE TRIGGER [reject part] ON [dbo].[rPro_HardnessTester] 
FOR INSERT
AS
	IF Exists(select * from inserted where hardnesstestervalue < '30')
	BEGIN
		EXEC master..xp_cmdshell 'net send mycomputer New EP1 rejected parts for hardness'
	END

Also if '30' is an integer value, remove the quotes. if it isn't, you may get false alerts or fail to get some you should

2nd, is messenger service running on both the server and your machine?

fugazi
Starting Member

6 Posts

Posted - 2009-09-02 : 07:20:45

thanks a lot Russell !!

another question, what should be the syntax to create a trigger on a view ? I just want to be advise when a new entry has been made in a view, i'm trying this one and it's not working :

CREATE TRIGGER [new reject part] ON [dbo].[vw_HardnessTester]
FOR INSERT
AS
EXEC master..xp_cmdshell 'net send mycomputer New rejected parts entry for hardness'

What's the problem ?

thanks again Russell !

Daniel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources