Display pop up message - SQL Server Forums

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

Display pop up message

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-08-29 : 09:38:25

Is there a way to have a trigger display a warning message if a particular value is trying to be saved to a table?

Table: oeordlin
Field: Status

If the Status is S I want a message to say "Item is Service Item"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 09:41:01

Yes. You can raise a message of difference severity level.
But only as a message, not as a dialogbox.

E 12°55'05.63"
N 56°04'39.26"

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-08-29 : 09:45:21

I'm not sure I'm following.

So I would not be able to display a message that read "Item is a Service Item"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 09:51:43

Yes. You can display a message but not as a messagebox.
You can write a trigger that discards whole statement, or only discards the "S status" records.
You have BEFORE and AFTER triggers for that.

E 12°55'05.63"
N 56°04'39.26"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-29 : 10:01:02

SQL Server is a database server application. There is no user-interface. It's just a service that runs in the background. It can send emails, or output text files, or add rows to tables, or return error messages to client apps, or do things like that, but it cannot display pop-up messages since there is no user sitting at the computer who is "running" SQL Server to see it. Your client applications need to handle that.

- Jeff
http://weblogs.sqlteam.com/JeffS

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-08-29 : 10:03:55

so I can't add a condition to this that if the status is S raise error

CREATE TRIGGER [Serviceitemerror] ON [dbo].[oeordlin_sql]
for INSERT
AS

RAISERROR('This is a service item', 16, 1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 10:05:58

I wrote you can.
It depends what you want to do with the other records (if there are any) in same insert that is NOT status s.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-29 : 10:06:32

Is this?

CREATE TRIGGER [Serviceitemerror] ON [dbo].[oeordlin_sql]
for INSERT
AS

IF EXISTS(Select * from inseted where status='S')
RAISERROR('This is a service item', 16, 1)

Madhivanan

Failing to plan is Planning to fail

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-08-29 : 10:19:00

That is exactly what I was looking for. To throw a little twist in there. I need to get the status from another table. Would it be something like this?

CREATE TRIGGER [Serviceitemerror] ON [dbo].[oeordlin_sql]
for INSERT
AS

IF EXISTS(Select * from inserted join imitmidx_sql on inserted.item_no = imitmidx_sql.item_no where imitmidx_sql.pur_or_mfg='S')
RAISERROR('This is a service item', 16, 1)
from inserted join imitmidx_sql on inserted.item_no = imitmidx_sql.item_no

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-08-29 : 10:21:23

Never mind, just noticed the extra FROM at last line. Took that out and it works good.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 10:22:21

quote:
Originally posted by Vack

That is exactly what I was looking for. To throw a little twist in there. I need to get the status from another table. Would it be something like this?

CREATE TRIGGER [Serviceitemerror] ON [dbo].[oeordlin_sql]
for INSERT
AS

IF EXISTS(Select * from inserted join imitmidx_sql on inserted.item_no = imitmidx_sql.item_no where imitmidx_sql.pur_or_mfg='S')
RAISERROR('This is a service item', 16, 1)
~~from inserted join imitmidx_sql on inserted.item_no = imitmidx_sql.item_no~~

no need of last part

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 10:24:23

[code]CREATE TRIGGER trgServiceItemError ON dbo.oeordlin_sql
AFTER INSERT
AS

IF EXISTS(SELECT * FROM INSERTED AS i INNER JOIN imitmidx_sql AS x ON x.item_no = i.item_no WHERE x.pur_or_mfg = 'S')
RAISERROR('This is a service item', 16, 1)[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 10:26:44

quote:

Originally posted by Peso

CREATE TRIGGER trgServiceItemError ON dbo.oeordlin_sql
AFTER INSERT
AS

IF EXISTS(SELECT * FROM INSERTED AS i INNER JOIN imitmidx_sql AS x ON x.item_no = i.item_no WHERE x.pur_or_mfg = 'S')
	RAISERROR('This is a service item', 16, 1)

E 12°55'05.63"
N 56°04'39.26"

Is there any difference between using AFTER & FOR?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-29 : 11:33:15

No. They are the same.
FOR is kept for backward compatibility.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-29 : 13:19:10