September 6, 2007 at 7:42 pm
Hi all,
I have created the trigger for audit purpose.
I just wanted to have on column to be insterted if any new record inserted or updated exist one.
I have table testaudit with columns of testid int and timestamp with default timestamp datatype
Alter
TRIGGER testaudit
ON
test
FOR
UPDATE, INSERT
AS
declare
@ikey int, @ukey int
SELECT
@ikey = testKey FROM inserted
SELECT
@ukey = testKey FROM updated
GO
declare
@ikey int, @ukey int
SELECT
'Before INSERT'
INSERT
test(testKey)
VALUES
(@ikey)
SELECT
'After Update'
INSERT
testAudit (testKey)
VALUES
(@ukey)
would any one please tell me that if this trigger is correct or not!!
Thanks
Pat
September 6, 2007 at 10:08 pm
Currently your trigger doesn't do anything except assing values to a parameter.
a couple things. You should always construct triggers to work whether 1 row is inserted, or more than 1 row is inserted
so this will not work
declare @ikey int, @ukey int
SELECT @ikey = testKey FROM inserted
SELECT
@ukey = testKey FROM updated
second,
There is no such thing as an Updated table. There are 2 virtual tables available within triggers.
Inserted, and Deleted.
so on update Inserted contains the records as they are being updated to, and the Deleted table contains the records prior to the update.
and since your trigger is not doing anything you will not see anything in your audit.
Try
Alter TRIGGER testaudit
ON
test
FOR
UPDATE, INSERT
AS
insert into testaudit(TestKey)
SELECT
testKey
FROM inserted
GO
September 7, 2007 at 12:37 am
HI guys,
I have created a proc that tries to create all triggers for insert/update and delete auditing
http://qa.sqlservercentral.com/scripts/viewscript.asp?scriptid=1950
see if this helps
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 12, 2007 at 5:03 pm
Hi that worked but i have to make some more changes
can i do if testkey exist then ony update the timestamp = getdate() else insert?
create
TRIGGER [dbo].[ispaudit]
ON
[dbo].[test]
FOR
UPDATE, INSERT
AS
if
exists (select distinct servicekey from testaudit)
then
update
testAudit
set
[timestamp] = getdate()
where
testkey = inserted.testkey
else
INSERT
testAudit (testKey)
select
testkey from inserted
end
if
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply