May 7, 2012 at 10:33 pm
Hi all,
I am new to sql server, How to write a trigger which stops the DML operation on a table for particular time...
Can anyone help on this......
May 7, 2012 at 11:50 pm
May 8, 2012 at 1:42 am
I haven't done this before. But you can use the REVOKE command inside your trigger to alter the privileges of a User.
But, I don't think you can do it for some period of time. The privileges would remain revoked until the next grant.
May 8, 2012 at 3:48 am
One way you can disable the trigger by using following sql script
ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name
Other way programaticlly handle it by checking a condition inside the trigger statement.
May 8, 2012 at 4:06 am
rraja (5/7/2012)
Check this.
One way you can disable the trigger by using following sql script
ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name
Other way programaticlly handle it by checking a condition inside the trigger statement.
I don't think the OP wants to disable the trigger. I think he wants to build a trigger which would revoke DML Priviledges of a User for a given period of time.
May 10, 2012 at 3:22 am
I think you can use an INSTEAD OF trigger for this purpose
Never had to use it myself though:-)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 10, 2012 at 4:03 am
maheshkrishnas (5/7/2012)
Hi all,I am new to sql server, How to write a trigger which stops the DML operation on a table for particular time...
Can anyone help on this......
What do you mean by "for particular time"? The following trigger will reject any DML for period from 8:00am to 12:00am for any day:
create trigger dbo.tr_MyTable on dbo.MyTable
after insert,update,delete
as
begin
if datepart(hour,getdate()) between 8 and 11
begin
raiserror ('Cannot change data in MyTable right now, please wait for better time',16,1);
rollback;
end
end
May 10, 2012 at 5:12 am
maheshkrishnas (5/7/2012)
Hi all,I am new to sql server, How to write a trigger which stops the DML operation on a table for particular time...
Can anyone help on this......
As suggested above you need to use Insead OF or After trigger. This may help you to understand better:
IF OBJECT_ID('MyTestTable') IS NOT NULL
BEGIN
DROP TABLE MyTestTable
END
GO
CREATE TABLE MyTestTable
(
SomeId INT IDENTITY(1,1),
SomeValue NVARCHAR(100) DEFAULT ('SomeValue_'+ CAST(NEWID() AS NVARCHAR(90))) ,
SomeTime DATETIME DEFAULT (GETDATE())
)
GO
INSERT INTO MyTestTable DEFAULT VALUES
GO 500
GO
CREATE TRIGGER TRG_MyTestTable ON MyTestTable
INSTEAD OF INSERT
AS
BEGIN
DECLARE @CurrentTime DATETIME
SET @CurrentTime = GETDATE()
DECLARE @SomeId INT
DECLARE @SomeValue NVARCHAR(100)
DECLARE @SomeTime DATETIME
SELECT @SomeId = SomeId FROM inserted
SELECT @SomeValue = SomeValue FROM inserted
SELECT @SomeTime = SomeTime FROM inserted
IF (DATEPART(HOUR,@CurrentTime) < 9 OR DATEPART(HOUR,@CurrentTime) >= 18)
BEGIN
RAISERROR ('No updates are allowed before or after business hours.',16,1)
ROLLBACK;
END
ELSE
BEGIN
IF (@SomeId <=0)
BEGIN
INSERT INTO MyTestTable(SomeValue,SomeTime) VALUES (@SomeValue,@SomeTime)
END
ELSE
BEGIN
SET IDENTITY_INSERT MyTestTable ON
INSERT INTO MyTestTable (SomeId,SomeValue,SomeTime) VALUES (@SomeId,@SomeValue,@SomeTime)
SET IDENTITY_INSERT MyTestTable OFF
END
END
END
May 10, 2012 at 6:13 am
I wouldn't use "Instead Of" trigger for that. The standard one for "after insert,update,delete" will do as fine with much less coding...
May 10, 2012 at 6:22 am
Eugene Elutin (5/10/2012)
I wouldn't use "Instead Of" trigger for that. The standard one for "after insert,update,delete" will do as fine with much less coding...
Depends on the reason for it. If the reason for preventing DML is to avoid long locks when another process is running, an after trigger doesn't help at all.
FYI, the INSTEAD OF trigger above isn't how you should write a trigger as it would not give correct results for multiple row inserts. Inserted and Deleted are tables and need to be treated as such.
May 10, 2012 at 6:38 am
HowardW (5/10/2012)
Depends on the reason for it. If the reason for preventing DML is to avoid long locks when another process is running, an after trigger doesn't help at all.
FYI, the INSTEAD OF trigger above isn't how you should write a trigger as it would not give correct results for multiple row inserts. Inserted and Deleted are tables and need to be treated as such.
Thanks for pointing that out Howard. If you can explain it a bit, I can try to improve it.
May 10, 2012 at 6:43 am
HowardW (5/10/2012)
Eugene Elutin (5/10/2012)
I wouldn't use "Instead Of" trigger for that. The standard one for "after insert,update,delete" will do as fine with much less coding...Depends on the reason for it. If the reason for preventing DML is to avoid long locks when another process is running, an after trigger doesn't help at all.
FYI, the INSTEAD OF trigger above isn't how you should write a trigger as it would not give correct results for multiple row inserts. Inserted and Deleted are tables and need to be treated as such.
1. Agree about case with avoiding locks, in this case only INSTEAD OFF can be really used, as it doesn't need to call rollback to prevent data modifications. AFTER trigger cannot really stop data modifications, it can only rollback them.
2. Right again, it's not a way to write a trigger, it applies to all DML triggers INSTEAD OF and AFTER ones ...
May 10, 2012 at 6:46 am
Divine Flame (5/10/2012)
HowardW (5/10/2012)
Depends on the reason for it. If the reason for preventing DML is to avoid long locks when another process is running, an after trigger doesn't help at all.
FYI, the INSTEAD OF trigger above isn't how you should write a trigger as it would not give correct results for multiple row inserts. Inserted and Deleted are tables and need to be treated as such.
Thanks for pointing that out Howard. If you can explain it a bit, I can try to improve it.
No problem. In for example this code here:
DECLARE @SomeId INT
DECLARE @SomeValue NVARCHAR(100)
DECLARE @SomeTime DATETIME
SELECT @SomeId = SomeId FROM INSERTED
SELECT @SomeValue = SomeValue FROM inserted
SELECT @SomeTime = SomeTime FROM inserted
If this was trigger from (for e.g.) an insert of 100 rows, inserted is a table with 100 rows, so you're only capturing 1 (arbitrary) row's data.
Instead, it should look something like this:
INSERT INTO MyTestTable(SomeID, SomeValue,SomeTime)
SELECT SomeID, SomeValue, SomeTime FROM INSERTED
So that it correctly deals with all the rows
May 10, 2012 at 6:50 am
HowardW (5/10/2012)
Divine Flame (5/10/2012)
HowardW (5/10/2012)
Depends on the reason for it. If the reason for preventing DML is to avoid long locks when another process is running, an after trigger doesn't help at all.
FYI, the INSTEAD OF trigger above isn't how you should write a trigger as it would not give correct results for multiple row inserts. Inserted and Deleted are tables and need to be treated as such.
Thanks for pointing that out Howard. If you can explain it a bit, I can try to improve it.
No problem. In for example this code here:
DECLARE @SomeId INT
DECLARE @SomeValue NVARCHAR(100)
DECLARE @SomeTime DATETIME
SELECT @SomeId = SomeId FROM INSERTED
SELECT @SomeValue = SomeValue FROM inserted
SELECT @SomeTime = SomeTime FROM inserted
If this was trigger from (for e.g.) an insert of 100 rows, inserted is a table with 100 rows, so you're only capturing 1 (arbitrary) row's data.
Instead, it should look something like this:
INSERT INTO MyTestTable(SomeID, SomeValue,SomeTime)SELECT SomeID, SomeValue, SomeTime FROM INSERTED
So that it correctly deals with all the rows
Right, got the point. Thanks Howard.
May 10, 2012 at 6:52 am
...
Depends on the reason for it. If the reason for preventing DML is to avoid long locks when another process is running, an after trigger doesn't help at all...
Actually, how Instead Off trigger can help here? I can see it will only be helpful if you want to ignore DML not prevent it as such.
Until, Instead Of trigger writes everything into temporary storage area for a duration of restriction...
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply