Determine the type of trigger

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:
>

Design of tables with large optional fields?

I have a general SQL design-type question.

I want to log errors to a table. If the error is with a URL, I want to store the URL. These URLs can be very large, hundreds of characters, but I only need to store it if it causes the error, which should be very infrequent. Which is the better design:

Create a large varchar field in the log table to hold the URL, or null if the error wasn't with the URL. Create a foreign key field in the log table to a second URL table, which has a unique ID and a large varchar, and only create a record in this table if the error is with the URL.

One concern I have with design 2 is that there could be many other fields that are infrequent. Do I create a separate table for every one?

Richard

There is no right solution I think, only the one you feel most comfortable with.
You should avoid NULL columns but it is not always possible.
|||After significant research, the definitive answer seems to be to create a separate table with a column for the optional data and a foreign key column to the original table. You can access all the optional values with a view or outer join.

Design of tables with large optional fields?

I have a general SQL design-type question.

I want to log errors to a table. If the error is with a URL, I want to store the URL. These URLs can be very large, hundreds of characters, but I only need to store it if it causes the error, which should be very infrequent. Which is the better design:

Create a large varchar field in the log table to hold the URL, or null if the error wasn't with the URL.

Create a foreign key field in the log table to a second URL table, which has a unique ID and a large varchar, and only create a record in this table if the error is with the URL.

One concern I have with design 2 is that there could be many other fields that are infrequent. Do I create a separate table for every one?

Richard

There is no right solution I think, only the one you feel most comfortable with.
You should avoid NULL columns but it is not always possible.|||After significant research, the definitive answer seems to be to create a separate table with a column for the optional data and a foreign key column to the original table. You can access all the optional values with a view or outer join.

Design of tables with large optional fields?

I have a general SQL design-type question.

I want to log errors to a table. If the error is with a URL, I want to store the URL. These URLs can be very large, hundreds of characters, but I only need to store it if it causes the error, which should be very infrequent. Which is the better design:

Create a large varchar field in the log table to hold the URL, or null if the error wasn't with the URL. Create a foreign key field in the log table to a second URL table, which has a unique ID and a large varchar, and only create a record in this table if the error is with the URL.

One concern I have with design 2 is that there could be many other fields that are infrequent. Do I create a separate table for every one?

Richard

There is no right solution I think, only the one you feel most comfortable with.
You should avoid NULL columns but it is not always possible.
|||After significant research, the definitive answer seems to be to create a separate table with a column for the optional data and a foreign key column to the original table. You can access all the optional values with a view or outer join.