January 31, 2007 at 1:35 pm
I have a database where I want to add a trigger function. I have been at it for two days, but am not sure the syntax I have set up. I have set two triggers: date_trigger and modify_trigger
CREATE TRIGGER date_trigger ON [dbo].[mydevice]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @date datetime
SELECT @date = (SELECT getdate())
CREATE TRIGGER modify_trigger ON dbo.mydevice
FOR INSERT, UPDATE, DELETE
AS
IF (SELECT type_id FROM inserted) > 0
IF (SELECT imei FROM inserted) > 0
IF (SELECT sim FROM inserted) > 0
IF (SELECT phone FROM inserted) > 0
IF (SELECT clarify FROM inserted) > 0
IF (SELECT acct_id FROM inserted) > 0
IF (SELECT status_id FROM inserted) > 0
IF (SELECT sapid FROM inserted) > 0
IF (SELECT notes FROM inserted) > 0
IF (SELECT coor_id FROM inserted) > 0
IF (SELECT exec_id FROM inserted) > 0
IF (SELECT esn FROM inserted) > 0
IF (SELECT serial FROM inserted) > 0
IF (SELECT imsi FROM inserted) > 0
IF (SELECT aup FROM inserted) > 0
IF (SELECT msg_id FROM inserted) > 0
IF (SELECT oncall FROM inserted) > 0
UPDATE mydevice set type_id = (SELECT esn FROM inserted)
UPDATE mydevice set imei = (SELECT coor_id FROM inserted)
UPDATE mydevice set sim = (SELECT esn FROM inserted)
UPDATE mydevice set phone = (SELECT esn FROM inserted)
UPDATE mydevice set clarify = (SELECT esn FROM inserted)
UPDATE mydevice set acct_id = (SELECT esn FROM inserted)
UPDATE mydevice set status_id = (SELECT esn FROM inserted)
UPDATE mydevice set sapid = (SELECT esn FROM inserted)
UPDATE mydevice set notes = (SELECT esn FROM inserted)
UPDATE mydevice set coor_id = (SELECT esn FROM inserted)
UPDATE mydevice set exec_id = (SELECT esn FROM inserted)
UPDATE mydevice set esn = (SELECT esn FROM inserted)
UPDATE mydevice set serial = (SELECT esn FROM inserted)
UPDATE mydevice set imsi = (SELECT esn FROM inserted)
UPDATE mydevice set aup = (SELECT esn FROM inserted)
UPDATE mydevice set msg_id = (SELECT esn FROM inserted)
UPDATE mydevice set oncall = (SELECT esn FROM inserted)
WHERE id = (SELECT id FROM inserted)
I want to know who makes changes to the entities of the device and to which device the change was made with a timestamp.
Will this work for what I need done? and Is being written correctly?
Thanks in advance,
Matt
January 31, 2007 at 5:25 pm
Ok - the problem is you do not understand how inserts, updates and deletes are actually implemented by SQL Server. Most of this is copied from BooksOnLine:
Two special tables are used in trigger statements: the deleted table and the inserted table. SQL Server 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.
The deleted table stores the BEFORE image during DELETE and UPDATE statements but is always empty for INSERTS.
The inserted table stores the AFTER image during INSERTS and UPDATE statements but is always empty for DELETE.
Recommend for inserts, use defaults and then a trigger is not needed.
For updates, see the below trigger source.
For deletes, the row is gone, so there is no row to store the last updated time or user.
Create table Devices
( DeviceIdinteger not null
, DeviceNamevarchar(255)not null
, LastUpdateTsdatetimenot null
constraint Devices_D_LastUpdateTs default getdate()
, LastUpdateLoginNamenvarchar(128)not null
constraint Devices_D_LastUpdateLoginName default suser_sname()
, constraint Devices_P primary key (DeviceId)
)
go
-- Do not allow anyone to specify values
deny insert, update on Devices ( LastUpdateTs, LastUpdateLoginName ) to public
go
create trigger Devices_tua
on Devices
for update
as
set nocount on
set xact_abort on
IF 0 = (select count(*) from inserted )
RETURN
IFUPDATE( DeviceId )
BEGIN
RAISERROR('DeviceId cannot be updated',16,1)
ROLLBACK
RETURN
END
UPDATE Devices
setLastUpdateTs= getdate()
,LastUpdateLoginName = suser_sname()
from inserted
whereDevices.DeviceId= inserted.DeviceId
go
-- Test cases
delete from Devices
insert into Devices
( DeviceId , DeviceName )
values (1 , 'The First Device')
insert into Devices
( DeviceId , DeviceName )
values (2 , 'The Second Device')
select * from Devices
UpdateDevices
set DeviceName = 'Changed DeviceID = 1'
whereDeviceId = 1
select * from Devices
UpdateDevices
set DeviceName = 'MultiRowUpdate ' + DeviceName
select * from Devices
delete from Devices
whereDeviceId = 2
select * from Devices
SQL = Scarcely Qualifies as a Language
February 1, 2007 at 6:50 am
Matthew, you should also check BOL for usage of the IF statement, which basically works two ways:
IF (expression)
(only the next statement is executed if expression is True)
the second way is like this:
IF (expression)
BEGIN
(all these statements are executed if expression is True)
END
February 1, 2007 at 6:51 am
Ah ha. Thanks Carl. After I posted this, I figured out a few things about delete and insert.
BooksOnline looks like it will help my novice experience.
Thanks again,
Matt
February 1, 2007 at 7:54 am
Here is a shortcut to BOL from SQL Query Analyzer: highlight a term, such as "create trigger" and then press SHIFT-F1 - BOL will automatically open.
Good Luck
SQL = Scarcely Qualifies as a Language
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply