TRIGGERS - 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 2008 Forums

Transact-SQL (2008)

TRIGGERS

Author

Topic

chinlax
Starting Member

30 Posts

Posted - 2011-10-11 : 04:01:54

Hi All,

I want to create a trigger for the employ table
which allows insert, updates ,delete only on week days not in sundays and also time between after 9 and before 6.

How to do this. give me ideas how to proceed.

Thanks in advance

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-11 : 04:10:13

create trigger x on employ
as
if datepart(hh,getdate()) not between 6 and 18
or datename(dw,getdate()) in ('Saturday','Sunday')
begin
raiserror('Updates not allowed at this time', 16, -1)
rollback tran
end

Depending on your system you might not need the rollback.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 04:15:59

why should you do this via triggers? why not diable the link/page at front end for employee addition. doing this in trigger means you capture all values and do db call simply and doing check and returning error. Is it really required to do a db call for this? I would have handled this in application layer disabling link/page for the period defined.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-11 : 04:29:01

Maybe because it needs to prevent direct access updates too?

Could also change the access for the role/users to read only but that might cause problems if the command was blocked and also wouldn't prevent dbo updates.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 05:17:51

ok that makes sense. otherwise its a bit of overkill to do it at db end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources