Trigger help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trigger help

Author

Topic

wonky donkey
Starting Member

15 Posts

Posted - 2007-08-23 : 12:52:31

Hi guys, need some help with a Trigger. The code below works fine, the only thing i'm missing is a row count of how many rows are in any table thats being created in the database (which this trigger is activated by). Is this possible, i know its possible to count rows once the table is created but i want them to be counted as soon as they have activated this trigger and then it can spit the figure out into the audit table.

cheers

USE [Business]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [Staff_Audit2]
ON DATABASE
FOR CREATE_TABLE
AS

INSERT dbo.Audit2
(AuditDate, SysUser, Application)
SELECT GetDate(), suser_sname(), APP_NAME()

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Staff_Audit2] ON DATABASE

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-23 : 22:39:58

Try conut rows in inserted table.

wonky donkey
Starting Member

15 Posts

Posted - 2007-08-24 : 07:09:21

Hi, sorry to change thread but i have another related question which is actually more urgent than the rowcount. :-) Does anyone know if its possible for a trigger to be fired when a table is updated through a data flow task in SSIS.?

The scenario is this. I have an audit trigger on SQL server 2005 that monitors details of tables created. This works fine when you create a new table with code or you create a table using an SQL statement in SSIS, and it even works if you use a data flow task in Integration services. However, the data flow method only works once and every day when the table is rebuilt the trigger doesnt fire again (i'm assuming as the data flow task becomes more of an update than a brand new create table??). I can write a trigger to look at individual rows that are updated but i just want to know each day that the table has been recreated. It is, but it isnt (if that makes sense), its really being overwritten and i dont know if thats a DDL function within a trigger than can be used??

Any guidance on this would be great! apologies if the above is confusing!!

my code is as below that works fine when you create a brand new table

USE [Business]
GO

CREATE TRIGGER [Staff_Audit1]
ON DATABASE
FOR CREATE_TABLE

AS

INSERT dbo.Audit2
(AuditDate, SysUser, Application)
SELECT GetDate(), suser_sname(), APP_NAME()

GO

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-24 : 07:25:39

Can't you just create an INSERT/UPDATE trigger?

Jim

CREATE TRIGGER [Staff_Audit1]
ON DATABASE
AFTER INSERT, UPDATE

INSERT dbo.Audit2
(AuditDate, SysUser, Application)
SELECT GetDate(), suser_sname(), APP_NAME()

wonky donkey
Starting Member

15 Posts

Posted - 2007-08-24 : 08:22:26

Hi, unfortunately just doing an update trigger doesnt work. The update and insert functions are not valid when you are dealing with a DDL at the database level, i think they can only be used at the table level.

Perhaps what i'm looking for doesnt exist and maybe i will just have to try and put a trigger on a table for when there is a bulk insert??

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-08-24 : 08:50:31

Just to be clear, are you saying that you're creating the table when you define the destination adapter in your data flow task (i.e. selecting "new table" as the destination?) If so, then it will be this bit of DDL that is captured by the trigger. This will not be the case when the package executes, unless you explicitly drop and recreate the table it in a preceding Execute SQL task.

Mark

wonky donkey
Starting Member

15 Posts

Posted - 2007-08-24 : 09:01:31

Hi there

The current data flow picks up a table from access and then the destination is the server. In the destination section of the data flow task there is a create table statement which fires the trigger the very first time you run the package. Its the subsequent times that the trigger isnt fired.

I think i get what you're saying. If i delete the table first, then perform the data flow it should be picked up every time??? I did think that myself but wasnt sure.

thanks for the help! :-)

wonky donkey
Starting Member

15 Posts

Posted - 2007-08-24 : 09:22:15

AAAARRRGGGGGHHH!!!!!

I'm having problems setting up the SSIS flow now. I have an sql statement to Drop the table from the server, then i have a make table to create the table, then a have a data flow to insert the data. However, the data flow will not execute due to it saying there isnt a table on the server by that name.

Am i doing this in the wrong logical way?

wonky donkey
Starting Member

15 Posts

Posted - 2007-08-24 : 09:27:19

Apologies! got it working now. Thanks very much for all your help!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-08-24 : 10:30:57

Did you set DelayValidation to True on the data flow task?

Mark

wonky donkey
Starting Member

15 Posts

Posted - 2007-08-24 : 10:48:19

Hi, i didnt change any of the setttings and it seems to work ok now. I have an sql statement to drop the table, an sql statement to create the table and then a data flow task that populates the table. It all seems to work ok. Also, whenever the package executes i get a new row in my audit log saying a table has been created. Essentially i'm replacing the table every day but thats what used to happen anyway soalthough its not the best use of resources its a decent workround.

My only problem now is trying to count the rows that are in the table that has been created. Any ideas? i've tried a row count but i just get 1 (i.e. the number of tables created.....not thr number of rows in it)

cheers

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-08-24 : 11:03:39

Stick a row count transformation into your data flow and then use the variable that this outputs to within a subsequent execute sql task to update the audit table. It goes without saying that there's no point in doing the count within your DDL trigger, as the number of rows in a newly created table is always going to be zero! (unless it's created by a SELECT INTO statement).

Mark

wonky donkey
Starting Member

15 Posts

Posted - 2007-08-24 : 11:12:41

of course! as you say, when i create the new table the trigger fires, but the data isnt in the table for it to be counted yet!

thanks very much for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources