I would like to know how to determine what type of trigger is occurring -
I am getting syntax errors with the following code
CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
If delete then
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER),'Delete', INSERTED.* FROM Inserted
End If
If insert then
INSERT INTO AuditAbatements SELECT GETDATE(),convert(char(30),
CURRENT_USER),'Insert', INSERTED.* FROM Inserted
End If
If update then
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER),'Update', INSERTED.* FROM Inserted
End If
ENDThe inserteD and deleteD (note the D on the end) virtual tables used inside
of triggered represent changes made to the table the trigger is 'ON' by a
single transaction. For a transaction that is an INSERT, the inserted virtua
l
table will contain the new rows to be added (the deleted virtual table will
be empty). For a transaction that is a DELETE, the deleted virtual table wil
l
have the rows to be removed (the inserted virtual table will be empty). For
a
transaction that is an UPDATE, the deleted virtual table will have the old
(current) values, and the inserted virtual table will have the new values
specified by the update.
Your code fails because you can't use the keywords
'insert','delete','update' in a boolean expression like you did.
You need something like this
CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @.insertedcount int
DECLARE @.deletedcount int
SELECT @.insertedcount = COUNT(*) FROM inserted
SELECT @.deletedcount = COUNT(*) FROM deleted
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER),
CASE
WHEN @.insertedcount = 0 THEN 'DELETE'
WHEN @.deletedcount = 0 THEN 'INSERT'
ELSE 'UPDATE'
END,
INSERTED.* FROM Inserted
END--
"CSHARPITPRO" wrote:
> I would like to know how to determine what type of trigger is occurring -
> I am getting syntax errors with the following code
> CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
> FOR INSERT, UPDATE, DELETE
> AS
>
> BEGIN
> If delete then
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),'Delete', INSERTED.* FROM Inserted
> End If
> If insert then
> INSERT INTO AuditAbatements SELECT GETDATE(),convert(char(30),
> CURRENT_USER),'Insert', INSERTED.* FROM Inserted
> End If
> If update then
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),'Update', INSERTED.* FROM Inserted
> End If
> END
>|||if this is the actual code, then the problem is in the "if delete |
insert | update then" lines. There is no such statement.
[There is an UPDATE() function that is available inside a trigger to
determine if a particular column has updated rows, but that would be
different than the attempted statement(s)]
Also, in addition to the inserted virtual table, there is a deleted
virtual table that is present for updates and deletes.
Look up CREATE TRIGGER in BOL for more details on using these.
there's no error if you try to access these virtual table and they
aren't there, so you can have a single insert
you will need to explicitly name your columns, instead of using inserted.*
also, this example assumes, as in the op, that only the modified rows'
values are being put into the audit table.
e.g.
create trigger [trgTest] on [dbo].[Abatements]
for insert, update, delete
as
begin
insert into AuditAbatements (<col names here> )
select getdate(), convert(char(30), current_user),
case when inserted.pk_col is null then 'Delete'
when deleted.pk_col is null then 'Insert'
else 'Update' end,
isnull(inserted.column1, deleted.column1),...etc...,
isnull(inserted.columnN, deleted.columnN)
from inserted
full join deleted on inserted.pk_col = deleted.pk_col
end
CSHARPITPRO wrote:
> I would like to know how to determine what type of trigger is occurring -
> I am getting syntax errors with the following code
> CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
> FOR INSERT, UPDATE, DELETE
> AS
>
> BEGIN
> If delete then
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),'Delete', INSERTED.* FROM Inserted
> End If
> If insert then
> INSERT INTO AuditAbatements SELECT GETDATE(),convert(char(30),
> CURRENT_USER),'Insert', INSERTED.* FROM Inserted
> End If
> If update then
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),'Update', INSERTED.* FROM Inserted
> End If
> END
>|||Thanks Mark,
You have really helped me today!
"Mark Williams" wrote:
> The inserteD and deleteD (note the D on the end) virtual tables used insid
e
> of triggered represent changes made to the table the trigger is 'ON' by a
> single transaction. For a transaction that is an INSERT, the inserted virt
ual
> table will contain the new rows to be added (the deleted virtual table wil
l
> be empty). For a transaction that is a DELETE, the deleted virtual table w
ill
> have the rows to be removed (the inserted virtual table will be empty). Fo
r a
> transaction that is an UPDATE, the deleted virtual table will have the old
> (current) values, and the inserted virtual table will have the new values
> specified by the update.
> Your code fails because you can't use the keywords
> 'insert','delete','update' in a boolean expression like you did.
> You need something like this
> CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
> FOR INSERT, UPDATE, DELETE
> AS
> BEGIN
> DECLARE @.insertedcount int
> DECLARE @.deletedcount int
> SELECT @.insertedcount = COUNT(*) FROM inserted
> SELECT @.deletedcount = COUNT(*) FROM deleted
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),
> CASE
> WHEN @.insertedcount = 0 THEN 'DELETE'
> WHEN @.deletedcount = 0 THEN 'INSERT'
> ELSE 'UPDATE'
> END,
> INSERTED.* FROM Inserted
> END--
>
> "CSHARPITPRO" wrote:
>|||Well, I mislead you a little bit too. There is a problem with the last
trigger that I posted. When the CASE expression evaluates to a DELETE action
,
the SELECT that results will be empty, because the inserted virtual table is
empty on DELETE transactions.
CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @.insertedcount int
DECLARE @.deletedcount int
SELECT @.insertedcount = COUNT(*) FROM inserted
SELECT @.deletedcount = COUNT(*) FROM deleted
IF (@.insertedcount = 0)
BEGIN
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER), 'DELETE', DELETED.* FROM Inserted
END
IF (@.deletedcount = 0)
BEGIN
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER), 'INSERT', INSERTED.* FROM Inserted
END
IF (@.insertedcount = @.deletedcount)
BEGIN
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER), 'UPDATE', INSERTED.* FROM Inserted
END
END
"CSHARPITPRO" wrote:
> Thanks Mark,
> You have really helped me today!
> "Mark Williams" wrote:
>
Showing posts with label trigger. Show all posts
Showing posts with label trigger. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Determine if a trigger exists.
I need to know how to determine if a specific trigger exists a on table and
if it does not i need to create it. Is this possible?
ThanksHi,
You may be able to use the following statement, but check the BOL
documentation for OBJECT_ID() because I'm not totally sure. Perhaps once
you've checked it out (or tested it) you can let the rest of us know how you
got along?
IF OBJECT_ID('dbname.owner.triggername') IS NULL
CREATE TRIGGER ...
Cheers,
Robert
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:684C9984-6AB3-41B2-8752-CB45BD8F00E5@.microsoft.com...
>I need to know how to determine if a specific trigger exists a on table and
> if it does not i need to create it. Is this possible?
> Thanks|||Hi, Robert
CREATE TRIGGER must be the first statement in a batch, so using it in
an IF statement won't work. There are (at least) two ways of dealing
with this:
a) drop the trigger if it exists and then create it:
IF OBJECT_ID('triggername') IS NOT NULL
DROP TRIGGER triggername
GO
CREATE TRIGGER ...
b) create the trigger if it doesn't exists, using EXEC('...'):
IF OBJECT_ID('triggername') IS NULL
EXEC('CREATE TRIGGER ...')
I preffer the first way, because:
1. By re-creating the trigger, I am sure that I have the correct
version of the trigger.
2. Using EXEC with a string means that I have to replace any
apostrophes in the trigger definition with double apostrophes.
Razvan|||Thanks for the help, I will try this today.
Thanks again!
"Razvan Socol" wrote:
> Hi, Robert
> CREATE TRIGGER must be the first statement in a batch, so using it in
> an IF statement won't work. There are (at least) two ways of dealing
> with this:
> a) drop the trigger if it exists and then create it:
> IF OBJECT_ID('triggername') IS NOT NULL
> DROP TRIGGER triggername
> GO
> CREATE TRIGGER ...
> b) create the trigger if it doesn't exists, using EXEC('...'):
> IF OBJECT_ID('triggername') IS NULL
> EXEC('CREATE TRIGGER ...')
> I preffer the first way, because:
> 1. By re-creating the trigger, I am sure that I have the correct
> version of the trigger.
> 2. Using EXEC with a string means that I have to replace any
> apostrophes in the trigger definition with double apostrophes.
> Razvan
>
if it does not i need to create it. Is this possible?
ThanksHi,
You may be able to use the following statement, but check the BOL
documentation for OBJECT_ID() because I'm not totally sure. Perhaps once
you've checked it out (or tested it) you can let the rest of us know how you
got along?
IF OBJECT_ID('dbname.owner.triggername') IS NULL
CREATE TRIGGER ...
Cheers,
Robert
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:684C9984-6AB3-41B2-8752-CB45BD8F00E5@.microsoft.com...
>I need to know how to determine if a specific trigger exists a on table and
> if it does not i need to create it. Is this possible?
> Thanks|||Hi, Robert
CREATE TRIGGER must be the first statement in a batch, so using it in
an IF statement won't work. There are (at least) two ways of dealing
with this:
a) drop the trigger if it exists and then create it:
IF OBJECT_ID('triggername') IS NOT NULL
DROP TRIGGER triggername
GO
CREATE TRIGGER ...
b) create the trigger if it doesn't exists, using EXEC('...'):
IF OBJECT_ID('triggername') IS NULL
EXEC('CREATE TRIGGER ...')
I preffer the first way, because:
1. By re-creating the trigger, I am sure that I have the correct
version of the trigger.
2. Using EXEC with a string means that I have to replace any
apostrophes in the trigger definition with double apostrophes.
Razvan|||Thanks for the help, I will try this today.
Thanks again!
"Razvan Socol" wrote:
> Hi, Robert
> CREATE TRIGGER must be the first statement in a batch, so using it in
> an IF statement won't work. There are (at least) two ways of dealing
> with this:
> a) drop the trigger if it exists and then create it:
> IF OBJECT_ID('triggername') IS NOT NULL
> DROP TRIGGER triggername
> GO
> CREATE TRIGGER ...
> b) create the trigger if it doesn't exists, using EXEC('...'):
> IF OBJECT_ID('triggername') IS NULL
> EXEC('CREATE TRIGGER ...')
> I preffer the first way, because:
> 1. By re-creating the trigger, I am sure that I have the correct
> version of the trigger.
> 2. Using EXEC with a string means that I have to replace any
> apostrophes in the trigger definition with double apostrophes.
> Razvan
>
Determine database no longer in use - Redux
I've attempted a number of methods to capture whether
specific databases are in use. I've configured an alert
for each one to trigger if the number of Active
Transactions exceeds 0. If this occurs, a job is run
which sends me an email via SMTP.
The question I have right now is what qualifies as an
Active Transaction? I can't find any reference to the
specific performance conditions in the BOL, just how to
set them up. I make changes to a test database and
sometimes the alert fires and sometimes it doesn't.
Thanks for any insight.
AllenAllen
One way you could do it is to detach the databases you
think are not being used and wait to see if anyone
complains. If you do make a mistake you can attach again
very quickly.
Depending on your company and what the databases are used
for you may or may not be able to do this. I know I would
not be able to, but it would work.
You could also use profiler to see if there is any
activity on a database. You might have issues with that if
you need to run it for several days and you have other
active databases on the same server.
Hope this helps
John
John
specific databases are in use. I've configured an alert
for each one to trigger if the number of Active
Transactions exceeds 0. If this occurs, a job is run
which sends me an email via SMTP.
The question I have right now is what qualifies as an
Active Transaction? I can't find any reference to the
specific performance conditions in the BOL, just how to
set them up. I make changes to a test database and
sometimes the alert fires and sometimes it doesn't.
Thanks for any insight.
AllenAllen
One way you could do it is to detach the databases you
think are not being used and wait to see if anyone
complains. If you do make a mistake you can attach again
very quickly.
Depending on your company and what the databases are used
for you may or may not be able to do this. I know I would
not be able to, but it would work.
You could also use profiler to see if there is any
activity on a database. You might have issues with that if
you need to run it for several days and you have other
active databases on the same server.
Hope this helps
John
John
Thursday, March 22, 2012
Determin the order of triggers,
Hi all,
Can one determine the order of triggers, or set the order of triggers. I
have a two insert triggers, generally I would like one trigger to fire first
all the tme. Is this possible.
RobertYou can specify which trigger fires first (or last) using
sp_settriggerorder. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Bravery" <me@.u.com> wrote in message
news:%23nKBoCWMGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Can one determine the order of triggers, or set the order of triggers. I
> have a two insert triggers, generally I would like one trigger to fire
> first
> all the tme. Is this possible.
> Robert
>|||You can set the first and the last trigger, i you have less than three
that shouldn=B4t be the problem:
sp_settriggerorder=20
HTH, Jens Suessmeyer.|||Hi
u can decide the order of triggers by the help of a Stored Procedure named
'sp_settriggerorder'.The syntax is as follows:
exec sp_settriggerorder <triggername>,<order>,'<operation>'
For Example:
exec sp_settriggerorder trig_Customer,first,'Insert'.
For more Info,log on to
http://www.sqlservercentral.com/col...erswhatsnew.asp
--
Regards
Ashish Narang
AIM: ashishnarang02
YIM: ashish_jsn@.yahoo.com
Mobile: +919820625179
"Jens" wrote:
> You can set the first and the last trigger, i you have less than three
> that shouldn′t be the problem:
> sp_settriggerorder
> HTH, Jens Suessmeyer.
>|||Thanks to all, for helping and poinmting in the right direction
Robert
"Robert Bravery" <me@.u.com> wrote in message
news:%23nKBoCWMGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Can one determine the order of triggers, or set the order of triggers. I
> have a two insert triggers, generally I would like one trigger to fire
first
> all the tme. Is this possible.
> Robert
>
Can one determine the order of triggers, or set the order of triggers. I
have a two insert triggers, generally I would like one trigger to fire first
all the tme. Is this possible.
RobertYou can specify which trigger fires first (or last) using
sp_settriggerorder. See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Bravery" <me@.u.com> wrote in message
news:%23nKBoCWMGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Can one determine the order of triggers, or set the order of triggers. I
> have a two insert triggers, generally I would like one trigger to fire
> first
> all the tme. Is this possible.
> Robert
>|||You can set the first and the last trigger, i you have less than three
that shouldn=B4t be the problem:
sp_settriggerorder=20
HTH, Jens Suessmeyer.|||Hi
u can decide the order of triggers by the help of a Stored Procedure named
'sp_settriggerorder'.The syntax is as follows:
exec sp_settriggerorder <triggername>,<order>,'<operation>'
For Example:
exec sp_settriggerorder trig_Customer,first,'Insert'.
For more Info,log on to
http://www.sqlservercentral.com/col...erswhatsnew.asp
--
Regards
Ashish Narang
AIM: ashishnarang02
YIM: ashish_jsn@.yahoo.com
Mobile: +919820625179
"Jens" wrote:
> You can set the first and the last trigger, i you have less than three
> that shouldn′t be the problem:
> sp_settriggerorder
> HTH, Jens Suessmeyer.
>|||Thanks to all, for helping and poinmting in the right direction
Robert
"Robert Bravery" <me@.u.com> wrote in message
news:%23nKBoCWMGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Can one determine the order of triggers, or set the order of triggers. I
> have a two insert triggers, generally I would like one trigger to fire
first
> all the tme. Is this possible.
> Robert
>
Subscribe to:
Posts (Atom)