Can I create triggers like that ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Old Forums

CLOSED - General SQL Server

Can I create triggers like that ?

Author

Topic

SlashZer0
Starting Member

4 Posts

Posted - 2004-10-12 : 04:09:20

I have a table that will be will contain the name of other tables when they are used for my work.
What I want to do is to create a trigger on the first table which will automaticaly create triggers on tables that i will refer in it.
is it possible ? i don't find any related topic over internet.

for example, i've a table T1 where i store the names of many other tables T2, T3, T4.. when they have to be used...
So, when when i add the name of T2 into T1, i would like a trigger which to somethiong like this :

create trigger trg on T1
for insert
as
...
create trigger trg1 on (the Name og my inserted name table)
...

Best Regards

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-12 : 04:14:58

have you tried creating and testing it first? i can foresee danger here:

you need to check if the trigger exists then do not create one, when do you create or not create a trigger (to avoid recursion that doesn't exit)

--------------------
keeping it simple...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-12 : 04:17:40

well you can do this with after insert trigger.
in the trigger you need to create a dynamic sql statement which then you execute.

hope this helps...

Go with the flow & have fun! Else fight the flow

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-12 : 04:18:17

man....

again by jen the yak hunter...

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-12 : 04:34:49

lol...

i'm actually at work and i can't do anything until our netadmin finalizes his part of the process (i've promised myself not to overstep other people's responsibilities), so while waiting for them to decide i got some time learning through this forum. i might stumble with some pretty neat stuff on automating admin tasks (being lazy and all that)

--------------------
keeping it simple...

SlashZer0
Starting Member

4 Posts

Posted - 2004-10-12 : 04:35:45

thanks for your answers...
yes, it's what i want to do, check my first table to see if the name is referenced, if no, create the trigger on it,
but each time i try to submit my code to sql server, it tells me it has bad syntax, and i don't find any example to write it correct...
i tried my different code, i'm not an expert in coding triggers so ... have look at my code..

CREATE TRIGGER TrgLtgc ON [dbo].[ListeTablesGestionCommerciale]
FOR INSERT
AS
BEGIN
CREATE TRIGGER TrgDateAgences ON [dbo].[Agences]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @TableName VARCHAR(50)
SET @TableName = 'Agences'

IF(SELECT COUNT(*) FROM DateDernierAcces WHERE nom_table = @TableName)!=0
UPDATE DateDernierAcces SET date_dernier_acces = GETDATE() WHERE nom_table = @TableName
ELSE
INSERT INTO DateDernierAcces (nom_table, date_dernier_acces) VALUES(@TableName, GETDATE())
END
END

Best Regards

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-12 : 04:36:02

heh... badminton works for you sleep?

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-12 : 04:48:21

i'm happy to inform you, YES!

after 1 week of playing badminton, i've been sleeping before 2 pm till 7 am. what an improvement. but i have to sweat it out for more than 2 hours, else still can't sleep. i guess the body just gets tired after all that activity...

my next activity will be swimming (at my age, don't know how to swim!) or tennis. i don't want to stick to badminton forever, not good to stick to one skill, else my medical friend told me, my body will get used to the pace. good alternative to drinking lol

--------------------
keeping it simple...

SlashZer0
Starting Member

4 Posts

Posted - 2004-10-12 : 04:52:36

very funny ;) lol
sport is good for health .. :)
cool ... that doesn't solve my problem :p
thks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-12 : 04:53:09

quote:
Originally posted by SlashZer0

CREATE TRIGGER TrgLtgc ON [dbo].[ListeTablesGestionCommerciale]
FOR INSERT
AS
BEGIN
CREATE TRIGGER TrgDateAgences ON [dbo].[Agences]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @TableName VARCHAR(50)
SET @TableName = 'Agences'

IF(SELECT COUNT(*) FROM DateDernierAcces WHERE nom_table = @TableName)!=0
UPDATE DateDernierAcces SET date_dernier_acces = GETDATE() WHERE nom_table = @TableName
ELSE
INSERT INTO DateDernierAcces (nom_table, date_dernier_acces) VALUES(@TableName, GETDATE())
END
END

Best Regards

errrr... you need to treat the next create trigger statement as string and execute it using exec sp_executesql, then before doing that, check for existence first using
if exists (select * from sysobjects where type='tr')

--------------------
keeping it simple...

SlashZer0
Starting Member

4 Posts

Posted - 2004-10-12 : 04:58:15

ok :)
i understand why my second trigger was refused ...
i 'll try with it, as string
thx a lot ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources