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

Designing Report in VS.NET2003 - studio is crashing

I am using reporting services 2000 already for 2 years. This problem is occurring on 2-3 reports, I can't say exactly what is the reason.

How I use the report designer is pretty simple and consistent. Report data is usualy produced by calling a stored procedure (on SLQ server 2000), I have a few parameters. Some of Paramaters are dates with a default values calculated to give yesterday's date or today - 7 days. And that is the only code in the report itselft. As soon as I click on Yes/No paramater, that doesn't have default, and is int in proc and report, then complete Visual Studio Environment starts flashing, making bip-bip noise, and is blocked, no access and reponse. I can't close it for a while, I can't bring up task manager, and in general this crash is very much resource intensive, Outlook is freezing, can't open IE, new spreadsheet. I am working on windows Xp platform. Eventually I would manage to close VS thru task manager.

As soon as this report is uploaded to Report Manager, the place where all the reports are available to users, on different server, the same report works fine.

Has anyone experienced this and what would be the reason.

Thanks in advance,
Elizabeta R

did you get out of memory exception.. check ur database may be it contains huge data..

run the query in sql server..