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

SQL Server 2000 Forums

Transact-SQL (2000)

TRIGGER

Author

Topic

princess82
Starting Member

10 Posts

Posted - 2006-07-12 : 10:01:54

i have this problem...what is the syntax let say if i want to use TRIGGER...if we want to enter the value that is already in the table, the trigger will appear as for exmple..please insert another value...
let say we have
tablename:airline
-airline_code
-airline_name

how to create a trigger that does not allow to insert the value that is already in the table.for vexmple the airline_code=013 is already in the table..so whenever, other user one to insert the value 013, the message 'enter other value' comes out..

princess82
Starting Member

10 Posts

Posted - 2006-07-12 : 10:26:26

ANOTHER PROBLEM IS

HOW TO MAKE USING THE TRIGGER...IF ANYBODY COME TO OFFICE ON SUNDAY,WE DO NOT ALLOW THEM TO INSERT/DELETE/UPDATE

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-12 : 10:35:02

Trigger doesn't have a client connection.
You can raise an error and reject the update and have the client display a message.

create trigger x on tbl for insert
as
if exists (select t.airline_code from tbl t join inserted i on i.airline_code = t.airline_code group by airline_code having count(*) > 1)
begin
raiserror ('duplicate airline_code', 16, -1)
rollback tran
end
go

Then the client looks for the error and displays the message you want.

You can do the same thing more easily, safely and efficiently by putting a unique index on airline_code.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-12 : 10:36:46

For the second
create trigger x on tbl for insert, update, delete
as
if datename(dw, getdate()) = 'SUNDAY'
begin
raiserror('no updates on sunday', 16, -1)
rollback tran
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources