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 2000 Forums
 Transact-SQL (2000)
 Trigger Update

Author  Topic 

elsietina
Starting Member

12 Posts

Posted - 2002-03-25 : 14:46:20
i need help writting an update trigger.

I have three columns in a table, tableID, username, and date.

what i want is on update, i want a count on the input(username) if it is less than three i want it to drop the userid from sql server login but if less than three nothing should happen.

Thanks

Jay99

468 Posts

Posted - 2002-03-25 : 16:32:26
I am gonna jump on the Celko bandwagon here . . .

quote:

Since you did not post any DDL, the most important part of any SQL problem, we can only guess or make general remarks without keys, constraints, DRI, etc.



That being said, here is a shot in the dark (probably contains errors, but should give you a general idea . . )


create trigger upd_table on table
for update
as

create table #dropme (
blah int identity(1,1),
username nvarchar(??)
)

declare
@blah int,
@maxblah int,
@sql nvarchar(300)

insert #dropme
select
t.username
from
[table] t
where
exists (
select
1
from
deleted
where
username = t.username)
group by t.username
having count(t.username) < 3

select
@blah = 1,
@maxblah = max(blah)
from
#dropme

while @blah <= @maxblah
begin
select
@sql = N'exec master..sp_droplogin '''+username+'''',
@blah = @blah + 1
from
#dropme
where
blah = @blah

exec master..sp_executesql @sql
end

go


Jay
<O>


Edited by - Jay99 on 03/25/2002 16:33:43
Go to Top of Page
   

- Advertisement -